Introduction
Sometimes, you may have experienced that your Microsoft SQL database is marked as SUSPECT. Database may go into SUSPECT mode because the primary filegroup is damaged and the database cannot be recovered during the startup of the SQL Server. Generally when the database is in SUSPECT mode, nobody can deal with the data.
Workaround
When the database is in SUSPECT mode, you can change the database status to the EMERGENCY mode. This could permit the system administrator read-only access to the database. Only members of the sysadmin fixed server role can set a database to the EMERGENCY state.
You can run the following SQL query to get the database to the EMERGENCY mode.
ALTER DATABASE dbName SET EMERGENCY
After that, you set the database to the single-user mode. Single user mode allows you to recover the damaged database.
ALTER DATABASE dbName SET SINGLE_USER
Then you can run DBCC CheckDB command. This command checks the allocation, structural, logical integrity and errors of all the objects in the database. When you specify “REPAIR_ALLOW_DATA_LOSS” as an argument of the DBCC CheckDB command, the procedure will check and repair the reported errors. But these repairs can cause some data to be lost.
DBCC CheckDB (dbName , REPAIR_ALLOW_DATA_LOSS)
If the above script runs without any problems, you can bring your database back to the multi user mode by running the following SQL command:
ALTER DATABASE dbName SET MULTI_USER
Recommendations
Using any DATA LOSS repair options can lead to other problems. This is not a recommended way to recover the database. The database should be restored from a backup made prior to the corruption, rather than repaired.
DBCC CheckDB command should be run on working databases at regular intervals to check for errors.
History
- 30th September, 2009: Initial post
