Tallan's Technology Blog

Tallan's Top Technologists Share Their Thoughts on Today's Technology Challenges

Recovering a SQL Database Marked ‘Suspect’

Overview

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:

image

I launched management studio and found the following:

image

Solution

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.

image

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.

image

image

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)
This command might take some time to execute (on my VM it took about 18 seconds, YMMV).
You should see “The error has been repaired” related to the errors you saw in the CHECKDB step previously:
image

Step 5: Set DB in MULTI_USER mode

Finally, we’re going to set the database back in MULTI_USER mode and hope we have a functional environment again.
Execute the following command:
ALTER DATABASE SharePoint_Config SET MULTI_USER
Refresh the databases node in SQL Management Studio again, and you should see the DB as back online.
image

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.

IT WORKED!!!

image

Conclusion

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.

No comments

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

\\\