How to repair a Suspect Database in SQL Server?
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
SOLUTION for this problem is quick and it is the following:
- Connect to your database server using Microsoft SQL Server Management Studio
- 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.