:::: MENU ::::

Repair SQL Server 2008 R2 Suspected Database

Just a day before , come across new issue of database . for some reason database found in suspect mode ! and that was horrible moment.

After some search , we got brief idea that , database can go in suspect mode for many reasons like improper shutdown of the database server, corruption of the database files etc.

To find out what exactly gone wrong with database we tried following query


(It wasn’t much helpful in my case . I am just posting here in-case it hepls other !)

After a while we run another block of code , to put the database in emergency mode (Refย  : MSDN )

EXEC sp_resetstatus ‘YOUR_DATABASE_NAME’;

Finally we got database back online !

Happy Coding .


  • Reply Prett Sons |

    Hello Vinay,

    Good to see your article on SQL database issues & you have written it very nicely!! I am Adam & I also love to write article on database issues.

  • Reply Frances |

    Thank you for sharing this information! I greatly appreciate that you took the time to write this and share.

    I am not a DBA and I really needed help.

  • Reply Anonymous |

    This is an excellent post. This is all I needed to fix the issue.
    Thanks a lot.

  • Reply Arshad |

    Thanks Vinay.

    Really it helps me to get my database back which was in suspect mode. I google everywhere that helps me but here i found out solution.

    Thanks once again.


  • Reply Shuja |


    Thank you for your help can you please let me now that how much time it will take to resolve my database from suspect to normal way?

    • Reply Vinay Patel |

      Hi ,

      I am not sure what parameters are affecting in recovery from suspected mode – in my scenario – it was 6 GB database and took about 20 minutes.


      • Reply Shuja |

        Actually Vinay my query till executing from 24 hours now can you please suggest
        me what should be my next step?

          • Shuja |

            My DB size is 24GB and i have applied this query
            EXEC sp_resetstatus ‘ServiceManager’;

            ALTER DATABASE ServiceManager SET EMERGENCY

            DBCC checkdb(‘ServiceManager’)


            DBCC CheckDB (‘ServiceManager’, REPAIR_ALLOW_DATA_LOSS)

            ALTER DATABASE ServiceManager SET MULTI_USER

            and now i am at this one
            DBCC CheckDB (‘ServiceManager’, REPAIR_ALLOW_DATA_LOSS)

            above 4 queries has been completed now i am at DBCC’s query.

          • Vinay Patel |

            Things are going right way – it seems your log is not committed and there are must be non- committed running transaction

            Your db is ready – just let it finish DBcheck to make sure after recovery data integrity is perfect !

  • Reply DJ |

    Thanks so much,

    I was even scared with the allow_data_loss
    But was surprised when I got my data.
    Thanks Mr Patel

  • Reply Dan K |

    This post has bailed me out twice in the past week now. Thanks very much!
    Any ideas why a db might enter “suspect” mode in the first place?

  • Reply Jerry Pyne |

    Thanks for the approach and comments but in the spirit of sharing I had a different problem that was simply solved by recycling the server.
    The problem was my fault because it was caused by running out of disk space.
    Disk space was added (by deleting old backups) but the Suspect did not get cleared using the above commands… Because of an unrelated problem the First alter failed (by the way you should enter the first two commands and get the results BEFORE executing the third command).

    Because of an unrelated problem and the first three commands ran forever (and there was NO disk or CPU activity). The stop button uncovered the failed alter. The “unrelated problem was associated with our tempdb growth and time outs in attempts to read the database.

    Restarting the SQL server fixed both problems but I would only do that if you get time outs on the tempdb and there is no I/O activity on the database.

    Good luck.

    • Reply Vinay Patel |

      Thanks for feedback !

      Actually , I tried to draft only the scenario what I have come across ! , And definitely will make sure to cover up all other aspects too


  • Reply Marrina |

    I used the above Queries to recover my database from Suspected mode, but when I execute
    DBCC checkdb(‘SGERP_EMPTYDB’) query I get the below error:

    Check statement terminated due to unrepairable error.

    But the database was recovered from Suspected mode but in this case I cant able to recover the datas

    Please help me (thanks in advance)

  • Reply Raju |

    Just Awesome Vinay ,, this is really very much Helpful to Repair the SQL Database..

    Give your mail ID


  • Reply Dave Rolph |

    I can’t thank you enough for posting this, like many other before me I tried a lot of googling and could not find an answer and was about to give up, I have been working hard on a new project and taking regular backups but lost 15 hours of work – which looks like is now back !

    Your the man ! thank you again. I will sleep tonight

  • Reply jaideep sarkar |

    Hi Vinay,
    This is in reference to the critical issue we had with MS SQL 2008R2 Cluster database.
    We are having eight nos. of instances installed on SQL cluster with 2 no. of cluster nodes.
    But recently we had got two of eight instances corrupted . And the SQL services are not getting online for only two instances & database attached with two instances got blanked out.
    Pls help out of it , this is a nightmare to us.

    • Reply Vinay Patel |

      Can you provide further information
      1# if log shipping is enabled on each instance ?
      2# do you have backup scheduled per node ?

      Please share further technical information


So, what do you think ?