Recovering a SQL Database Marked ‘Suspect’
If you’ve ever had to develop for SharePoint, you’ve likely had to develop using virtual machines from time to time. Given the sheer size these VM’s grow to, I generally keep them on external drives.
Yesterday, I accidentally pulled the USB 3.0 cable for my external drive before my machine went into sleep mode while the VM was still running. When I booted back up, I was met with the following error message in SharePoint:
I launched management studio and found the following:
Luckily, my colleague Brian Feldmann had been through this before and helped me find a solution to bring back a Suspect database.
Step 1: Set Emergency Mode
In order to connect to the database to start the repair, the database has to be put into Emergency mode. Execute the following:
USE master GO ALTER DATABASE SharePoint_Config SET EMERGENCY
If you refresh the databases node in object explorer, you should see the change reflected to the database.
Step 2: Run DBCC_CHECKDB
Next, run DBCC_CHECKDB against the database to check the integrity of the database.
DBCC CHECKDB (SharePoint_Config)
The output from the command will likely show errors. Some examples of the errors in my environment are below.
Step 3: Put DB in Single User Mode
The database needs to be in single user mode before we can repair.
ALTER DATABASE SharePoint_Config SET SINGLE_USER WITH ROLLBACK IMMEDIATE
You should see output similar to the following:
Nonqualified transactions are being rolled back. Estimated rollback completion: 0%. Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
Step 4: Run Repair
To repair the database we’re going to use DBCC_CHECKDB with the REPAIR_ALLOW_DATALOSS argument. This will attempt to repair the database and will allow us to put it back into a valid state, discarding anything that may have been being written as part of a transaction when the DB was disconnected.
Cross your fingers and fire off the following command:
DBCC CHECKDB (SharePoint_Config, REPAIR_ALLOW_DATA_LOSS)
Step 5: Set DB in MULTI_USER mode
ALTER DATABASE SharePoint_Config SET MULTI_USER
Step 6: Drumroll Please…..
Now that the DB is back online, time to check SP and see if the DB was too far gone to be repaired, or if the repair left it in an unusable state.
The steps above can help you recover your environment if you end up with a database in an unrecoverable state.
There are NO GUARANTEES that this will work for you, or that your DB will be in a usable state after the repair process. But given the amount of time it takes to set up an SharePoint DEV VM, it’s worth the 10-15 minutes of time to see if you can bring it back to life.