Background
When you see your database in Suspect mode, that is a code red situation. It's not something that you face everyday. It can happen, however, most often due to faulty hardware or operational mistakes (like deleting a transaction log file). This post will show how to recover from suspect mode, but still go through SQL-server error logs and find out the root cause of the error.
Reason
At start-up, SQL Server attempts to obtain an exclusive lock on the device file. If the device is being used by another process or if the file is missing, SQL Server starts displaying error.
In these cases, there is usually nothing wrong with the devices and database. For the database to recover correctly, the device must be made available, and the database status must be reset.
Possible reason for changed to suspect mode in SQL Server can be:
- The system cannot find the file specified during the creation/opening of physical device
- Failed to open device where data or log file resides
- SQL server went down/restarted in the middle of a transaction causes transactions log to be corrupted
- SQL server cannot access data or log file while coming online, because of your beloved antivirus
Solution
To resolve this issue, run the commands listed below:
EXEC sp_resetstatus ‘DATABASE_NAME’;
ALTER DATABASE DATABASE_NAME SET EMERGENCY
DBCC checkdb(‘DATABASE_NAME’)
ALTER DATABASE DATABASE_NAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB (‘DATABASE_NAME’, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE DATABASE_NAME SET MULTI_USER
What the above command does is perform a series of steps to check the logical & physical consistency of the database and tries to repair. The very first step is to turn off the suspect flag on a database, you can achieve this by using sytem stored procedure sp_resetstatus. Using this procedure, change the suspect flag to emergency. Once the database is set to EMERGENCY mode, it becomes a READ_ONLY copy and only members of sysadmin fixed server roles have privileges to access it. Then, perform a consistency check by executing DBCC command on the master database. The next step is to rollback any transactions if any are present in the database and bring the database into Single User mode. Run the repair and finally bring database to Multi User mode.
Remember sp_resetstatus can be executed only by the system administrator. Always shut down SQL Server immediately after executing this procedure.
If the database is still marked as suspect after performing these steps, there may be other problems preventing the database from recovering. At this point, the only option left is either restore from a good backup or set the database to emergency mode and use the bulk copy program copy the data out.
CodeProject
