SQL Database is in Suspected Mode

Question

I found that database is in

Suspected Mode ......

How Can I Recover it from this Mode.

Answer 1

Go through the SQL Server Error logs to find out the reasons for the database to go in to SUSPECT mode.Based on the reasons why the database went into suspect mode, the next course of action can be decided.

The easiest approach would be to restore from the good backups.

Answer 2

1. Firstly look in the SQL Server Logs in Enterprise Manager(EM). To get there, start EM and drill down from the Database Server … Management … SQL Server Logs. There should be multiple logs starting with Current and then followed by 6 or more archived logs.

2. Review all recent Error Log(s). There WILL be an indication here as to why the database has been marked suspect. You need to fix whatever the problem is first (i.e. missing file, permissions problem, hardware error etc.)

3. Then, when the problem has been fixed and you're either sure that the data is going to be ok, or you have no backup anyway, so you've nothing to lose, then change the database status to normal and restart SQL Server.

4. To change the database status we will us the following store procedure: sp_resetstatus. The steps are as follows:

   a.

   USE master
   GO
   sp_configure 'allow updates', 1
   GO
   RECONFIGURE WITH OVERRIDE
   GO 
   

   b.

   sp_resetstatus 
   GO
   -- Example: sp_resetstatus ‘Washington’ 
   

5. After the procedure is run, immediately disable updates to the system tables:

   sp_configure 'allow updates', 0
   GO
   RECONFIGURE WITH OVERRIDE
   GO 
   

6. Stop and Restart ALL SQL Server Services

7. If the database still goes back into suspect mode, and you can't fix the original problem, and you have no recent backup, then you can get information out of the database by putting it into Emergency Mode. If you do this, extract the data/objects out with DTS and rebuild the database. Note that the data may be corrupt or transactionally inconsistent. You WILL NOT be able to use this instance of the database after it is put in to Emergency Mode besides pulling data out of it !!!

8. Issue the following command to put the database into emergency mode:

   a.

   USE master
   GO
   sp_configure 'allow updates', 1
   GO
   RECONFIGURE WITH OVERRIDE
   GO
   

   b.

   UPDATE master..sysdatabases
   SET status = 32768
   WHERE name = 'DatabaseName'
   

9. Stop and Restart ALL SQL Server Services.

10. We are now ready to pull whatever data we can out of the tables in the corrupt database. Remember, some tables may be corrupt, thus you may have to play with various T-SQL statements to get the data out. First try DTS …

11. These are the steps necessary to export data out of the corrupt database into the new:

    a. Create a new production DB, or a temp DB, to have a place to export whatever data we can out of the corrupt db.

    b. Start a DTS operation by going into EM and drilling down to “Data Transformation Services” … “Local Packages”.

    c. Open a NEW DTS package by right-mouse clicking …

    d. When DTS opens, choose “Copy SQL Server Objects Task” from the Connection Icons. Enter in a description like “Export Corrupt Data”. Enter in the SA/pass combination as well as the CORRUPT database from the drop-down.

    e. Select the “Destination” Tab. ”. Enter in the SA/pass combination as well as the PRODUCTION database from the drop-down.

    f. Select the “Copy” Tab. UNCHECK the “Create destination objects” box. UNCHECK the “Copy all objects” box and then Click on the “Select Objects” Button. This brings up the “Select Objects” screen.

    g. CHECK ONLY “Show all tables” like shown above. Then check each table that needs to be exported. If ALL tables need to be export, Click on the “Select All” button. Click OK.

    ** If ALL objects are to be recovered, Select ALL Objects by check marking them and then click on the “Select All” button. This will grab everything possible.

    h. Click OK again and we are done creating this task. Now we execute the package by Clicking the green arrow on the menu bar.

12. Restore Data Issue: Restoring the data into the Production database is dependent on what time of day it is. If it is during “Hot” times, high playing times, restore the data during a slow period or close of gaming day!

If Microsoft Tech support is to be called, it is advisable to get the log files ready to be emailed to the tech for review. The process to accomplish this is as follows:

1. Go to the Bin folder located under the SQL Server installation folder. In this folder you will find an application called SQLDIAG.exe.

2. Execute SQLDIAG.exe. This app is “suppose” to zip all the log files into a file called SQLDIAG.txt in the Log folder under the SQL Server installation folder. This operation did NOT work for us.

3. We went directly into the Log folder of SQL Server and used PKZip to zip the files. We then emailed them directly to the tech.

Answer 3

Sam, what version of SQL do you have? In 2005 and 2008 update on master database is not allowed anymore. In case you don't have a Full backup here is what you need to do: After you identified the problem in SQL Server Log, follow the intructions here and here depending which is your case.