SQL Server 2008 R2 (Suspect) mode - how to repair?

Question

I have SQL Server 2008 R2 database in Suspect mode. I tried to fix it running this 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

But the repair output was this message:

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.

How to repair the database fully? This repair which i made, works only for some days, then database again goes to Suspect mode...

Answer 1

I ran into a similar issue. However, I was unable to get the database out of suspect as the backups were suspect as well. I used the exporting functionality in SSMS to move the tables and data to new blank database.

This however does not move all of your stored procedures, views, functions, etc. I have a copy of RedGate's SQL Compare (which I highly recommend to anyone working with SQL schemas on a regular basis) that handled all of the structure migration. So it made the process fairly painless. RedGate has a 14 day trial on their software if you need to use it.

If you only have tables, or a small amount of procedures, views, etc., You can use SSMS's "Generate Scripts" function to make creation scripts for these items, then run them on your new DB.

RedGate's SQL Compare: http://www.red-gate.com/products/sql-development/sql-compare/

Answer 2

You cannot repair this database, and the repair you've potentially made has not been complete. You should make sure to understand why your database is becoming corrupt - check your disk system as that's the most likely culprit.

Then restore your database from a backup - but make sure to run DBCC CHECKDB on that very backup to ensure it's not corrupt itself.

DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS is the very last resort you should ever go to. Before that, you should restore a valid backup. Once you do use REPAIR_ALLOW_DATA_LOSS, don't expect your database to magically come back to life again - most likely, irreparable damage has occurred - especially in this case since it complains about system table corruption.

Answer 3

Have you tried what Paul Randal recommends? Creating, detaching, re-attaching, and fixing a suspect database

1. Create a new dummy database with the exact same file layout and as close as possible to the file sizes of the detached database
2. Shutdown SQL Server
3. Swap the corrupt database files
4. Re-start SQL Server
5. Use emergency-mode repair

Answer 4

In my case I got the suspect status on my database by a power off.

Try the next query, it helped for me:

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