Suspectmode DB Recovery

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.

 Collapse

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.

 Collapse

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.

 Collapse

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:

 Collapse

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.

How to repair a SQL Server 2005 Suspect database

Sometimes when you connect to your database server, you may find it in suspect mode. Your database server won’t allow you to perform any operation on that database until the database is repaired.

A database can go in suspect mode for many reasons like improper shutdown of the database server, corruption of the database files etc.

To get the exact reason of a database going into suspect mode can be found using the following query,

DBCC CHECKDB (‘YourDBname’) WITH NO_INFOMSGS, ALL_ERRORMSGS

Output of the above query will give the errors in the database.

To repair the database, run the following queries in Query Analyzer,

EXEC sp_resetstatus ‘yourDBname’;

ALTER DATABASE yourDBname SET EMERGENCY

DBCC checkdb(‘yourDBname’)

ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB (‘yourDBname’, REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE yourDBname SET MULTI_USER