Please Read the follwoing documentation for
Recover Suspect SQL Server 2005 Database
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?
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
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
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.