I come into the office recently to find one of our VMware hosts was throwing a wobbly and had left several virtual machines in an unresponsive state. The option for guest OS shutdown was unavailable, so they needed to be force stopped and the host needed rebooting to get everything up and running again. After powering up the VMs, one of the SQL Server databases had been left in suspect mode.
This host issue is related to Veeam snapshots at the time of backups; however, this is an issue for another article.
These are the most common reasons for a database going into suspect mode:
In my case, the SQL database most likely crashed during a transaction where it lost connectivity to the disk.
Now for the reason why you’re reading this article; how to fix the database. If you have suitable backups, every man and his dog recommend to go down that route. If you’re like me, you want to see if you can recover the database first because in more cases than not, there will be a period of time between when it was last backed up and when the database went into suspect mode that you want to try and recover.
The following steps should be followed to repair the database:
EXEC sp_resetstatus databasename;
ALTER DATABASE databasename SET EMERGENCY;
DBCC CHECKDB (databasename);
ALTER DATABASE databasename SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DBCC CHECKDB (databasename, REPAIR_ALLOW_DATA_LOSS);
ALTER DATABASE databasename SET MULTI_USER;The above commands will set the database to EMERGENCY mode and allow you to attempt a repair which has to be done in SINGLE_USER mode. In my case this repair fixed the issue and brought the database back into normal operation.
If it works, I recommend executing the following:
DBCC CHECKDB (databasename);To make sure it comes back without any further warnings or repairs.
If you try and it fails with an error, try searching on how to fix your specific issue.
If CHECKDB with REPAIR_ALLOW_DATA_LOSS fails, you will have to resort to recovering from your last backup.