How to recover the suspect mode database in SQL Server 2008?

Question

To recover the database from suspect mode executes the following query.

EXEC sp_resetstatus ‘yourDBname’;
ALTER DATABASE yourDBname SET EMERGENCY
DBCC checkdb(’yourDBname’)
ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB (’yourDBname’, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE yourDBname SET MULTI_USER

Then getting

Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors

How to resolve this?

Answer 1

According to Paul Randal, this seems to be a bug in checkdb. Check this article from Paul which discusses about this issue.

The workaround is to run CHECKDB on the database with TABLOCK

Answer 2

Run DBCC CHECKDB without the repair option. What does it tell you? If the system tables are corrupt then it's game over and time to restore from backup.