Recovering a SQL Server Database from Suspect Mode

The next step was to perform a DBCC Checkdb along with Repair with Data Loss by executing the below T-SQL query against the master database.

DBCC CheckDB ('test_dr', REPAIR_ALLOW_DATA_LOSS)

This query will attempt to repair all reported errors. These repairs can cause some data loss.

Once the DBCC CheckDB with the Repair with Data Loss option were executed, the Database went into Single User mode as shown below:

After performing the above step the database was brought ONLINE and Multiple Users access was enabled by executing the below T-SQL query against the master database.

ALTER DATABASE test_dr SET MULTI_USER

Please refer the screen capture below.

As you can see from the above screen capture the database named test_dr is back ONLINE. I am even able to view its objects as shown below:

As final step for safety, I again checked the consistency of the database which was just repaired and brought ONLINE (i.e. the test_dr database) by executing the below T-SQL query against the master database.

 DBCC CheckDB ('test_dr')

After performing the above steps I ensured that all the required logins had access to the database with proper privileges. The application started working fine and the business was back on track. It took just 38 minutes to bring the SUSPECT database back ONLINE.

Please let me know if you have any comments on this approach or alternative approaches you have used in the past.

Pages: 1 2




Related Articles :

30 Responses to “Recovering a SQL Server Database from Suspect Mode”

  1. Hi Satnam,

    I’m curious: if the first checkdb chowed no errors why di you run the DBCC CheckDB (‘test_dr’, REPAIR_ALLOW_DATA_LOSS)?

    It’s a nice article

    Seth

    • After running the first command. my database is stuck in “RECOVERY_PENDING” state.

      can you tell me what are my options at this point?

      Thanks in adavnce

  2. Why run DBCC CHECKDB() WITH REPAIR_ALLOW_DATA_LOSS if there are no consistency errors in the database? There isn’t anything for the command to do, right?

  3. Very nice article!

  4. No backups so you had to repair?

    Emergency mode repair is the very last resort when all else (restoring from good backup) fails. It should not ever be the default action.

    As as result of the repair (which would have discarded the transaction log and built a new one), your database could be transactionally or even structurally inconsistent. You should verify constraints and do some analysis of the data to make sure that you don’t have some nasty anomalies as a result of that repair.

    To repeat, checkDB with repair_allow_data_loss is NOT the first thing that should be done in the case of log corruption. Only if there’s no good backup and no other way to get that DB back should it be considered.

  5. I have to agree with others. If you repaired the database allowing data loss, how is the business back on track?

    You got the database back online in 38 minutes but what data was lost in the process? Why not just restore it? Since it is a production database you would have log backups?

    Did you figure out what caused the corruption with the log file to begin with?

  6. There won’t be any existing data lost, the corruption was all in the log.

    That said, transactions that were active at the point of the DB going suspect could have been partially written to the data file and the other part lost during the rebuild.

    So, for eg, if there was a transaction that debited $10k from one account and credited it to another, the debit could have written to the data file and the credit just in the log and wiped out when the log was rebuild, or for another example, there could be OrderLines without an OrderHeader record or anything like that in fact. Transactional inconsistencies.

    • Hi
      Does this mean that the database has to be restored from backup and logs if any are to be reapplied in all cases and this approach should not be followed in the case of transaction log failure?

      Thanks
      Rama

      • Yes the database should be restored from backup and logs reapplied. That’s the first and generally recommended approach any time a DB is suspect, whether it’s from log or data file corruption.

        The only time that emergency mode repair should be the absolute last resort, for only when the important database has no backups and hence can’t be restored.

  7. Its a very nice article, have read all of your’s and they are so interesting covers each topic in depth. I believe Microsoft should award you with SQL Server MVP status.

  8. Very nice article, also see
    http://dbtweets.blogspot.in/

  9. My goodness, Why the Guys always resort to Emergency Mode whenever they encounter the the suspect status..

    You have to understand first why your database has gone into suspect status, Best way to do it ,is to look into the SQL Server Error log.
    Look for the Database backups and Log backups because that will help you to restore the database to nearest point of time .
    If you dont have backups then only resort to “Putting your database into Emergency mode” and then going ahead with the rest of the activities.

  10. If we have a large database in suspect mode and it is only due to the log file corruption (because we have a lot of other causes such as disk failure etc) … If this database in suspect mode has only one backup set and is about 650GB to be restored, with 4 hours in SLA do you really think this is the last resort we have to do? – Or still prefer losing money in penalty due to probably this restore from a BAckup will take hours and hours depending on where this backup set is stored (eg: tape , remote disks etc).

    See, this is just a question to see your point of view based on that situation, because I already went thru similar cases where I really had to go using the option to fix the actual database than going for restoring it from a backup set due to the 4 hours SLA.

    Regards.

  11. Excellant Projection. This help me to retrive my database.

    Thanks

  12. Worked for me

  13. If Log file damage,can’t you regenerate it ?

  14. After running the first command. my database is stuck in “RECOVERY_PENDING” state.

    can you tell me what are my options at this point?

    Thanks in advance

  15. I try this usually…
    1) copy the db files using windows explorer which should be possible since SQL should not be locking it.
    2) delete the db from sql.
    3) attach the files back. The original files should be there…step (1) is just extra precaution.
    4) run dbcc checkdb to confirm no corruption.
    We got into this sometimes when the disk subsystem is down when SQL starts…

  16. I am running this script and it just spins at executing Query for hours… How long should this take before you see a result? Is there a way to tell if it is actually doing anything?

  17. I run this script successfully. I brought the DB online . Thank you

  18. Thanks a lot, it was great help…

  19. I ran this script successfully and brought back my db. no data loss. Thanks for sharing this.

  20. It helped to restored my DB in order and I value the effort.

  21. I follow the these steps
    1)EXEC sp_resetstatus ‘ACDealer’;
    2)ALTER DATABASE ACDealer SET EMERGENCY
    3)DBCC checkdb(‘ACDealer’)
    4)ALTER DATABASE
    ACDealerSET SINGLE_USER
    WITH ROLLBACK IMMEDIATE
    The Above Two steps Successfully Cmpleted
    but
    In 3rd step
    DBCC checkdb(‘ACDealer’)

    Exectution Process Take Long Time Finally Got This Type Of Error
    “”Msg 8998, Level 16, State 2, Line 1
    Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 12 pages from (1:0) to (1:8087). See other errors for cause.
    Msg 8966, Level 16, State 2, Line 1
    Unable to read and latch page (1:1) with latch type SH. 1117(The request could not be performed because of an I/O device error.) failed.
    CHECKDB found 2 allocation errors and 0 consistency errors not associated with any single object.
    CHECKDB found 2 allocation errors and 0 consistency errors in database ‘ACDealer’.
    Msg 0, Level 20, State 0, Line 0
    A severe error occurred on the current command. The results, if any, should be discar”” Please Solve My Problem

    in this step i got

  22. thanks for this one… my server is up again.. :D

  23. I really wanto to thank you this article was helpfull for me. it did solve all my trouble with my database. Thanks again.

  24. Hi Satnam,

    Thanks a lot for this article.

    My server is up again after performing the above steps.

  25. Thanks . IT was very helpful for solving my problem.

  26. Hello mates, its great post on the topic of educationand entirely explained, keep it up all the time.

Trackbacks/Pingbacks

  1. Database in Suspect Mode – SQL Server « SQL DBA Support . COM – Knowledge Centre for SQL DBAs - June 13, 2012

    [...] http://www.sql-server-performance.com/2012/recovery-sql-server-suspect-mode/ [...]

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |