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:

1) Database is corrupted
2) Database files are being “opened” or held by some process (operating system, other program(s)…)
3) Not enough disk space for SQL Server
4) Insufficient memory (RAM) for SQL Server
5) Unexpected SQL Server shutdown caused by power failure etc…

SOLUTION for this problem as follows:

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.

Advertisements