Recovering SQL Server Database from Suspect Mode

Sometimes we have to face a critical situation when SQL Server database going to Suspect Mode. In that moment no work can be done on database. Database may go into suspect mode because the primary file group is damaged and the database cannot be recovered during the startup of the SQL Server

Reason for database to go into suspect mode:

  1.  Data files or log files are corrupt.
  2.  Database server was shut down improperly
  3.  Lack of Disk Space
  4.  SQL cannot complete a rollback or roll forward operation.

How to recover database from suspect mode:

1.    Change the status of your database. Suppose database name is “BluechipDB”

EXEC sp_resetstatus '';

Example:

EXEC sp_resetstatus 'BlueChipDB'

  1.  Set the database in “Emergency” mode
ALTER DATABASE  SET EMERGENCY;

Example:

ALTER DATABASE BlueChipDB SET EMERGENCY
  1.  Check the database for any inconsistency
DBCC CHECKDB('');

Example:

DBCC checkdb('BlueChipDB')

4. If you get any error after executing DBCC CHECKDB then immediately bring the database in SINGLE USER MODE by running following query. If no error found then you need not execute the following query.

ALTER DATABASE  SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Example:

 ALTER DATABASE BlueChipDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

5. For safety, take the backup of the database.

6. Run the following query as next step. Remember while using the below query, that uses REPAIR_ALLOW_DATA_LOSS, is a one way operation that is once the database is repaired all the actions performed by these queries can’t be undone. There is no way to go back to the previous state of the database. So as a precautionary step you should take backup of your database in step 5 mentioned above.

DBCC CHECKDB ('', REPAIR_ALLOW_DATA_LOSS);

Example:

DBCC CheckDB ('BlueChipDB', REPAIR_ALLOW_DATA_LOSS)

7. Finally, bring the database in MULTI USER mode

ALTER DATABASE  SET MULTI_USER;
ALTER DATABASE [BlueChipDB]  SET MULTI_USER
  1.  Refresh your database server and verify the connectivity of your database. Now users should be able to connect to the database properly. If any data loss, you can restore database – backup taken in step 5.
Please share, if it is helpfulShare on FacebookShare on LinkedInTweet about this on TwitterShare on Google+Email this to someonePrint this page

11 thoughts on “Recovering SQL Server Database from Suspect Mode

  1. Tobias Englund says:

    Thanks for your post, it helped me recover a database in our test environment and saved me lots of hours of work.

  2. SZARS says:

    it did not work for me. it says
    Msg 945, Level 14, State 2, Line 1
    Database ‘xxxxDB’ cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.

  3. Sanjeewa Gunasekara says:

    Thanks heaps Mahedee for valuable article. Today, a SQL server database has gone to suspect state after always on failover and your steps of recovery saved my day!!

    Great work – keep it up.

    Regards,
    Sanjeewa.

  4. atul says:

    I followed the steps but the command is taking a very very long time to execute. Any suggestion is appreciated.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>