Monday, 5 November 2012

Steps to recovering SQLServer database from Suspect Mode




Causes

SQL Server database may go to suspect mode due to 

1.    Unavailability or Corruption of one or more database files.
2.    Database server\service might not be shut down properly.
3.    Denial of access to a database resource by operating system.

Being in suspect mode, we can’t perform any transactions until we repair the database.

Step-1

Reset the status of the suspected database. sp_resetstatus turns off the suspect flag on a database. This procedure updates the mode and status columns of the named database in sys.databases.

Step-2

Change the suspected database state to EMERGENCY which gives READ_ONLY access to database and access is limited to members of sysadmin server role. EMERGENCY State is mainly used for troubleshooting purpose.

Step-3

Check the logical and physical integrity of all the objects in the specified database. This is performed through DBCC CHECKDB command.

DBCC CHECKDB command internally runs DBCC CHECKALLOC, DBCC CHECKTABLE and DBCC CHECKCATALOG. It also validates the contents of indexed views, link level consistency between table metadata and file system directories and files when storing VARBINARY (MAX) data in the file system using FILESTREAM and service broker data in the database.

Step-4

Change the database state to SINGLE_USER mode

Step-5

Run REPAIR_ALLOW_DATA_LOSS to ensure the database is returned to a structurally and transitionally consistent state. And please keep in mind that emergency mode repair is a one-way operation. Anything it does cannot be rolled back or undone. If this is an issue, then make a copy of the damaged database before we run emergency mode repair.

Step-6

Change the database state to MULTI_USER mode.

Consolidated Script

EXEC sp_resetstatus <<Database Name>>;
ALTER DATABASE <<Database Name>> SET EMERGENCY
DBCC checkdb('<<Database Name>>')
ALTER DATABASE <<Database Name>> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('<<Database Name>>', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE <<Database Name>> SET MULTI_USER