Monday, July 23, 2012

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

1 comment:

  1. If you are looking for a Mechanical Eng. Government jobs and want to make a career in Government Service then Apply urgent opening

    ReplyDelete