Database in Suspect or Recovery Pending State

Today one of the SQL 2008 R2 clusters that I manage lost a shared disk. The disk happened to be the DATA directory for SQL server. When the disk came back online it performed a chkdsk, and determined that there was corruption on the disk. After logging into the SQL instance I found a database in a Suspect state and another in a Recovery Pending State.

Losing a disk is a scarring thing, especially when the databases on that disk don’t come back online in a healthy state. The resolution is fairly strait forward for both situations:

  1. Set the database into EMERGENCY mode and turn on SINGLE USER mode:
  2. Check the database for potential errors:
  3. At this point, based on the errors you received, you need to decided what course of action need to be taken. Errors regarding loss of data might constitute restoring the database from backup; on the other had errors regarding bad indexes and partitions you might consider allowing DBCC CHECKDB to correct the issues. Ultimately the end of the DBCC CHECKDB results will tell the you minimum repair level to correct the issues that it discovered. Use the command below that corresponds to the results of your CHECKDB:
  4. After CHECKDB is complete the database should be in a healthy state. Now you can bring it back to multi-user mode:

  5.  

Leave a Reply

Your email address will not be published.

HTML tags are not allowed.