Recover database from suspect mode SQL Server 2008 (R2)

From last one month, I am researching on most common SQL Server database issues. I checked many forums as well as blog post & noticed that most of the SQL server database users suffer with “SQL database goes in suspect mode” issue.

 

What the exact meaning of this issue: The database goes to suspect mode due to improper system shutdown, database corruption, corrupted log file etc. You can find out the exact reason by following steps:

First run this query:

EXEC sp_resetstatus ‘DataBaseName’

 ALTER DATABASE [DataBaseName] SET EMERGENCY

 DBCC checkdb(‘DataBaseName’)

 ALTER DATABASE DataBaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE

 DBCC CheckDB (‘DataBaseName’, REPAIR_ALLOW_DATA_LOSS)

 ALTER DATABASE DataBaseName SET MULTI_USER

In 80% cases, the issue gets resolved but in 20% cases the new error message get arise such as:

Warning: You must recover this database prior to access.
Msg 8921, Level 16, State 1, Line 5
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
Warning: The log for database 'ServeDB' 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 8921, Level 16, State 1, Line 9
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
 

Note: May be you got different error message but in most of the cases the above error is same. 

Look at the Possible Solutions:

 
In this case, you cannot repair database easily by running above query, first you should understand why your database get corrupted. You can check it by running DBCC CHECKDB command. After that restore your database from updated backup - but make sure before restoring backup, it's not corrupt itself.


If it is happen, use SQL Database Recovery Software to repair SQL Server database. The Software works great with all latest versions of MS SQL Server including 2008 R2, 2008, 2005, 2000.