Recovering database from suspect mode
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.
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
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.