SQL Server

Repair a Suspect Database in SQL

There can be a possibility that when you connect to an SQL Server Instance you will find the database being marked as SUSPECT. During such scenarios, you will not be able to connect to the database. In this article we will go through the steps which you need to follow to recovery a database which is marked as SUSPECT. The steps mentioned in this article works on SQL Server 2005 and higher versions.

Some of the reasons why an SQL Server database can be marked as SUSPECT

  • Database could have been corrupted.
  • There is not enough space available for the SQL Server to recover the database during startup.
  • Database cannot be opened due to inaccessible files or insufficient memory or disk space.
  • Database files are being held by operating system, third party backup software etc.
  • Unexpected SQL Server Shutdown, Power failure or a Hardware failure.

Due to a hardware failure one of our database namely BPO was marked SUSPECT when the SQL Server came back online. Already due to the hardware failure we had downtime for more than two hours and adding to that when the server came back online our mostly critical database was marked as SUSPECT.

I am assuming you have a basic understanding of how to use SQL Query tool in the SQL admin studio. In this example, we will use the ReportServer DB. We are also assuming that you have repaired any of the above issues and done any restores needed.

  1. Run the following command

    Alter Database ReportServer set Emeregency

  2. Then check for any errors by running the following command. The very lst line of the output will report if there are errors, and you will need to deal with those before continuing

    DBCC CHECKDB (ReportServer)

  3. Set the datgabase to Single user mode

    ALTER DATABASE ReportServer SET SINGLE_USER WITH ROLLBACK IMMEDIATE

  4. Now we will repair the database. Just be careful, there may be some data loss, but most likely you know that by now.

    DBCC CHECKDB (ReportServer, REPAIR_ALLOW_DATA_LOSS)

  5. Then set the database to back multi user mode

    ALTER DATABASE ReportServer SET MULTI_USER

2 thoughts on “Repair a Suspect Database in SQL

Leave a Reply

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