I have SQL Server 2008 R2 database in Suspect mode. I tried to fix it running this query:

EXEC sp_resetstatus ‘yourDBname’;
DBCC checkdb(’yourDBname’)

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...

migrated from stackoverflow.com Jan 21 '12 at 23:28

This question came from our site for professional and enthusiast programmers.

  • This is an old post, but I was wondering if you remembered what happened prior to this happening? – swasheck Mar 22 '12 at 0:40
  • My ReportserverTempdb is under suspect mode I do not come out using all the above query can'you suggest some thing different – user26041 Jul 16 '13 at 16:30
  • Hi @Manojsingh and welcome to Database Administrators, perhaps you would like to ask a new question giving your details so folks can help you with this specific question? – jcolebrand Jul 16 '13 at 19:05
  • Let me just point out that when you set the option REPAIR_ALLOW_DATA_LOSS you might get data loss. Big surprise. – usr Apr 19 '14 at 21:05

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/


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.


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
  • NOTE: Those directions are for cases where the suspect database has been detached. Do not detach suspect databases. The OP doesn't seem to indicate that his database is detached. That said, other links from that article may be more directly relevant to the problem. – RDFozz Dec 28 '17 at 23:41

Not the answer you're looking for? Browse other questions tagged or ask your own question.