How to repair a Suspect Database in SQL Server?

SQL Server Posted on 32 185 views 9 comments

Suspect state of SQL Server database is a state when you are unable to connect to the database.
In this state you cannot do anything with your database: no opening, no backup and no restore.

Possible cause for this problem can be one of the following:

  • database is corupted
  • database files are being "opened" or held by some process (operating system, other program(s)...)
  • not enough disk space for SQL Server
  • insufficient memory (RAM) for SQL Server
  • unexpected SQL Server shutdown caused by power failure
  • etc...


SOLUTION for this problem is quick and it is the following:

  1. Connect to your database server using Microsoft SQL Server Management Studio
  2. Execute the following SQL script:
    NOTE: replace [DatabaseName] with your database name 
-- sp_resetstatus turns off the "suspect" flag on a database
EXEC sp_resetstatus [DatabaseName]

-- Marking database READ_ONLY, disable logging, 
-- and limiting access only to members of the sysadmin fixed server role
ALTER DATABASE [DatabaseName] SET EMERGENCY

-- Checks the logical and physical integrity of all the objects in the specified database
DBCC checkdb([DatabaseName])

-- This query will rollback any transaction which is running on that database 
-- and bring SQL Server database in a "single user" mode
ALTER DATABASE [DatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB ([DatabaseName], REPAIR_ALLOW_DATA_LOSS)

-- Set database accessibility to it's original state, allowing all logins
ALTER DATABASE [DatabaseName] SET MULTI_USER

Your database should now be accessible and no longer marked as "suspect" database.