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.

SQL server database can go in suspect mode for many reasons; some of them are given below:

Improper shutdown of the database server
Corruption of the database files
Unavailable device files
Unavailable database files
Database resource used by operating system
SQL Server incorrectly asserts free data page space when a row is inserted



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


SUPPOSE YOUR DATABASE NAME IS TREASURY THEN

DBCC CHECKDB (TREASURY) 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 TREASURY;

ALTER DATABASE TREASURY SET EMERGENCY

DBCC checkdb(TREASURY)

ALTER DATABASE TREASURY SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB (TREASURY, REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE TREASURY SET MULTI_USER

and you are done. 

You should keep one thing in mind while using the above queries that the repair mode used here , REPAIR_ALLOW_DATA_LOSS, is a one way operation i.e. once the database is repaired all the actions performed by these queries can’t be undone. There is no way to go back to the previous state of the database. So as a precautionary step you should take backup of your database before executing above mentioned queries.

Courtesy:-MRSUPPORT