When a database is in SUSPECT state that means SQL Server knows that the database is Inconsistent.

The best thing to do when you have a SUSPECT database is Restore a FULL backup. But if you are not having Backups (For the reason only God and you knows) than the only option left is to get the most you can and compromise in the consistency of the database.

There could be a case that you have to extract some data from your suspect database that you know is consistent and your backup is not having that info. Example a table you created after the backups and want that table in your restored database.

The steps to access the SUSPECT database are:

  1. Make sure that the database you are having is in SUSPECT mode.

1

  1. As the database is in SUSPECT mode that means we cannot access it. The best to do as mentioned earlier is Restore it from a Full backup. But if you don’t have it or you need something from this one. Than set the database is EMERGENCY mode.

2

You can see that the database is in EMERGENCY Mode.

3

 

  1. We have to run DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS. But as Emergency mode is a MULTI USER mode we have to set the the database in SINGLE USER mode.

4

  1. Now as the database is set in SINGLE_USER mode we can run DBCC CHECKDB on it.

5

The Information that we got is:

 

File activation failure. The physical file name “C:\AdventureWorks2012_Database\AdventureWorks2012_log.ldf” may be incorrect.

The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.

Failed to restart the current database. The current database is switched to master.

Warning: The log for database ‘AdventureWorks2012’ has been rebuilt. Transactional consistency has been lost. The RESTORE chain was broken, and the server no longer has context on the previous log files, so you will need to know what they were. You should run DBCC CHECKDB to validate physical consistency. The database has been put in dbo-only mode. When you are ready to make the database available for use, you will need to reset database options and delete any extra log files.

Msg 824, Level 24, State 2, Line 1

SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x0c660550; actual: 0x0c668550). It occurred during a read of page (2:0) in database ID 5 at offset 0000000000000000 in file ‘C:\AdventureWorks2012_Database\AdventureWorks2012_log.ldf’.  Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

 

  1. Set the database back to MULTI_USER mode.

6

Check the status of the database to confirm that it is ONLINE.

7

 

Recovering Suspect Database.

Leave a Reply