How to Repair Suspect Database in SQL Server with DBCC CHECKDB

Sometimes when connecting to the database, the database becomes inaccessible and you will get the following error message:

error message 945

The error message indicates that the database is in SUSPECT mode. In this article I am going to show you how to create a suspect database and it’s repair solution.

Now I am going to tell you the scenario of Suspect database in SQL Server manually and how to repair it.

CREATING THE SUSPECT DATABASE

  • Launch SQL Server Management Studio.
  • Launch SQL Server

  • I have created the database already named example, and I am going to fetch data from its table to check database accessibility.
  • fetch data from table

  • After fetching the data from the database table, close the connection to make the database SUSPECTED.
  • Now go to the Services by running services.msc and stop SQL Server services by right clicking on it.
  • Stop SQL server services

  • Now I am going to corrupt the log file of example database. For this I am going to use hex editor.
  • Launch Hex Editor. Open transaction log file that is example_log.ldf extension.
  • open .ldf in hex editor

  • We get the strings of that database. I am going to delete first four rows to make the log file corrupt.Save that corrupted file with CTRL+S.
  • delete and save strings using hex editor

  • Again, Turn on the services of SQL Server.
  • turn on the services of SQL

  • Connect to the database again.
  • Fetch the data of your database to check whether the database goes into Suspect mode or not.
  • When you fetch your data, you will get the following error message.
  • suspect database error

    This error message indicate that your database goes into the Suspect mode.

    POSSIBLE REASONS FOR SUSPECTED SQL DATABASE:

    • Having low disk space.
    • System failure.
    • Improper system shut-down.
    • Corrupted transaction log file.

    Caution: Things you shouldn’t perform during SUSPECT mode :

    When your database is in SUSPECT mode you cannot detach your database. You will get the following error, when you try to detach the suspected database. By doing this, you will loss all the possible chances of repairing a suspected database.

    Msg 3707, Level 16, State 2, Line 1

    Cannot detach a suspect or recovery pending database. It must be repaired or dropped.

    error 3707

    This error will not allow your database to get detached because it is in SUSPECT mode.

    Now comes to its Repair solution.

    How to Repair SUSPECT DATABASE in SQL Server

    • To repair your database from the suspect mode, you have to set your database in Emergency Mode by:
    • Alter Database [example] Set EMERGENCY;

    • Then permit single user mode to your database by:
    • Alter Database [example] set SINGLE_USER;

    • Then use below command to repair the database from the SUSPECT Mode.

      DBCC CHECKDB (N ‘example’, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;

    • Warning: Choose it as a last option as it leads to data loss.

      switch to single user

    • The database has been successfully repaired, and get into the Single user Mode.Now setting database from single user mode to multiple user.
    • fetch recovered data

    • Fetch your data from the database, you will finally get the recovered data.
    • CONCLUSION:

      In this blog section, I have discussed how to create a suspect database and how to repair SUSPECT database using Emergency mode. The blog discusses the possible reasons for Suspected database and things you should not perform when your database is in SUSPECTED Mode. You can also go for an alternate option like SQL Database Repair to recover your data.

      Advertisements

About Andrew Jackson

I am SQL DBA and SQL Server blogger too. I like to share about SQL Server and the problems related to it as well as their solution and also I do handle database related user queries, server or database maintenance, database management etc.
This entry was posted in T-SQL. Bookmark the permalink.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s