Recover Suspect SQL Server 2005 Database

Dear All

Please Read the follwoing documentation for

Recover Suspect SQL Server 2005 Database
Problem
one of our Digital university client’s SQL server  2005 database that has the wrong database status.  For some reason it is in the ‘Suspect\Offline’ mode.  And I have been told by project manager to check and correct the problem quickly and get the database back online.  How can I do so?   Once the status is correct, do I need to take any further steps? 

Solution

I have taken use of the script below to correct the SQL Server 2005 database status,.  We also need to correct any sort of corruption and understand why this occurred in the first place. 

Database Status Correction Script

The script below will change the database to be in simple recovery mode, which may or may not be the needed configuration for your database.  As such, it is necessary to review the database configurations once this script has been executed.  In addition, it is necessary to change the ‘DBName’ to your database name in single quotes.

EXEC sp_resetstatus ‘DBname’;

ALTER DATABASE DBname SET EMERGENCY

DBCC checkdb(‘DBname’)

ALTER DATABASE DBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB (‘DBname’, REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE DBname SET MULTI_USER

Check for Corruption

The next step in this process is very key.  It is necessary to determine if the database has any corruption and ensure that the database will be able to support the users.  If the database has corruption, we may be exposing ourself to more issues by just changing the database status without correcting the underlying issue.  To identify the underlying issue, execute the following commands:

  • DBCC CHECKDB – Validate the overall database integrity
  • DBCC CHECKCATALOG – Validate the system catalog integrity
  • DBCC CHECKTABLE – Validate the integrity for a single table

To resolve the issue, we may need to do one or more of the following:

  • Drop and Recreate Index(es)
  • Move the recoverable data from an existing table to a new table
  • Update statistics
  • DBCC UPDATEUSAGE
  • sp_recompile

To ensure the issue is corrected, it is a good idea to re-run the identification commands listed above and validate that they do not have any issues. 

Thanks

Prashant Deshpande

Advertisements

Leave a comment

Filed under Database Server, Server Application, SQL Server

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s