Recovering database from suspect mode

Share us

Recently I got an issues in one of the database in production environment where I found a database is in suspect mode. It happens some time because of services re-start activity which happened in my case. In such scenarios, we need to repair the database to make it online. These are the below steps need to follow to resolve such issues. Below is how database looks when it is in suspect mode.

Suspect_Mode

 

Step 1: First try to take database offline and then bring it online. If still database is in suspect mode, give another try,  re-start the services and see if suspect mode has gone or not. If not then need to follow step 2.

Step 2: Run below  stored procedure 

EXEC sp_resetstatus [YourDatabase];

step 3: Bring the database in emergency mode by using below command and now database will looks as in below screenshot,after refreshing the database.

ALTER DATABASE [YourDatabase] SET EMERGENCY

Emergency_Mode

Step 4:  Set the database in single user mode using below command:

ALTER DATABASE [YourDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Step 5: Run below command which is actually repairing the database.

DBCC CheckDB ([YourDatabase], REPAIR_ALLOW_DATA_LOSS)

Step 6: Set the database in multiuser mode using below command:

ALTER DATABASE [YourDatabase] SET MULTI_USER

After following all above steps database will be come online and we can fire the queries.

Feel free to share your view and comments.

 

2 Commentsto Recovering database from suspect mode

  1. DurgaBhavani. says:

    Thank you for sharing information.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.