How to recover database from suspect mode (above 2008)

Database can go into suspect mode for a couple of reasons, including, corruption in data files, indexes, logs, bad storage, less space available on storage for the database files to grow.
Following two ways may resolve your problem.

Step 1: Put database in emergency mode
                         ALTER DATABASE  dbName  SET  EMERGENCY

Step 2: Checks the logical and physical integrity of all the objects in the specified database
                         DBCC checkdb ('dbName')

If DBCC CHECKDB is successful then below Message will appear
CHECKDB found 0 allocation errors and 0 consistency errors in database 'DBA'. DBCC execution completed. 

Else DBCC shows any error message, fix the errors. In case of corrupted index , try to rebuild or re-create it.

Step 3: Put database in single user mode with rollback immediate
                         ALTER DATABASE dbName SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Step 4: Tries to repair all reported errors. These repairs can cause some data loss.
                         DBCC CHECKDB (dbName, REPAIR_ALLOW_DATA_LOSS)

Step 5: Put database in Multi user mode 
                         ALTER DATABASE dbName SET MULTI_USER

No comments:

Post a Comment