Recovering a SQL Server Database from Suspect Mode

A couple of days back at I
got a call from my support team informing me that one of our database located
on the Production Server went into Suspect
The version used was SQL Server 2005 Service Pack 3. Being a
Production Database server, it was a Priority 1 incident and the expected time
of resolution was 4 hours..


The first step was to identify why this incident occured and after investigation it was found that it was due to the corruption of the transactional log file of the database.

I connected to SSMS using the sa login
credentials and located the SUSPECT database:

I then reset the status of the SUSPECT Database by executing the below T-SQL query against the
master database.

EXEC sp_resetstatus 'test_dr';

sp_resetstatus turns off the suspect flag on a
database. This procedure updates the mode and status columns of the named
database in sys.databases. Also note that only logins having
sysadmin priveleges can perform this :

As you can see in the above screen capture, the T-SQL query
gave the warning message upon execution:

You must recover this database prior to access

The next step was to set the SUSPECT database into an EMERGENCY
mode. This was done by executing the below SQL query against the master


Once the database is set to EMERGENCY mode it becomes a READ_ONLY
copy and only members of sysadmin fixed server roles have privileges to
access it. The basic purpose for this is to facilitate troubleshooting. I did not want
other users updating the database while it was being worked on.

As you can see from the above screen capture, once the T-SQL
query got executed successfully the state of the database changed from SUSPECT

Once the database state was changed to EMERGENCY. I
then performrf a consistency check by executing the below T-SQL query
against the master database.

DBCC checkdb('test_dr')

Which resulted in the below output:

As seen from the above screen capture
there is no issue with respect to consistency of the test_dr database.
Also, this confirmed that the logical and physical integrity of the database was

The next step was to set the database to SINGLE USER
mode with ROLLBACK IMMEDIATE. To do this the below SQL query was
executed against the master database.


The above query will rollback any transactions if any are present
in the test_dr database and will bring the database named test_dr
into Single User mode.

Please refer to the screen capture below:

Pages: 1 2


37 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


    • 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?


      • 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

  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.


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


  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’;
    3)DBCC checkdb(‘ACDealer’)
    The Above Two steps Successfully Cmpleted
    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.

  27. Hi, a very good post it was the cause to solve my problem, many thanks.

  28. how to restore database in sql server and i didn’t set any transaction or save point
    and i have executed update command
    and i want to rollback to that…so please help me out.

  29. Thank you , that was great and very helpful
    you saved my life!

  30. Thank you very much for the post. It worked. It was nicely done and was very helpful.

  31. Weird engough We had a process that was killed on our production server that didn’t want to go away, so I couldn’t alter the DB, even though it was suspect, Restarted SQL, and it recovered itself.

  32. Thank you, i fix my problem because of this post.

    I did some preparation before doing your instruction.

    Thanks again :)

  33. Thank you so much. I had a problem with the transaction log on my development environment and this saved me loads of time. Very helpful :-)


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

    [...] [...]

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 |