Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
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 yesterday
add comment

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

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

3 Answers

up vote 4 down vote accepted

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/

share|improve this answer
add comment

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.

share|improve this answer
add comment

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
share|improve this answer
add comment

protected by Community Jul 16 '13 at 20:39

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?

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