This can happen following things like hardware failure, power outages, database files being locked by the Operating System (Anti-Virus, backup software etc.) or actual corruption of the database.
Attempting the repair procedure below is really a last resort. If you have good and recent backups, then if at all possible I would perform point-in-time recovery of the database concerned, as this emergency repair can (as the name suggests) result in data loss.
If you’ve gone through all of this like I once did though, and all other avenues failed, here are the steps that fixed it for me:
EXEC sp_resetstatus 'YourDBName' ALTER DATABASE 'YourDBName' SET EMERGENCY
(You won’t be able to query the database until it’s in EMERGENCY mode!)
DBCC CHECKDB ('YourDBName')
ALTER DATABASE YourDBName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CHECKDB ('YourDBName') WITH NO_INFOMSGS, ALL_ERRORMSGS
DBCC CHECKDB ('YourDBName', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE YourDBName SET MULTI_USER
In the end, I just had to rebuild a couple of indexes after the repair process and everything was recovered….phew!
ALTER INDEX ALL ON [YourTableName] REBUILD
Again, it’s worth stressing that this is a last resort – use a good backup/standby etc. first, if you have one!
Hi Garth,
Thanks for sharing this tips!!!
I want to share something about DBCC CHECKDB minimum repair options: repair_fast, repair_rebuilt & repair_allow_data_loss. Most of the users think minimum repair options are the last resort to recover database from suspect mode but it is not true. As you know, there is some amount of data loss in repair_allow_data_loss option. I recommend to try advanced sql database repair software to recover database from suspect mode if you don’t want to loss any data from database.
Thanks
Mark
Thanks for the share Mark 🙂
“DBCC CHECKDB (‘YourDBName’, REPAIR_ALLOW_DATA_LOSS)”
Good Afternoon,
I just wanted to make sure that anybody else reading this that the above command can lead to data loss which cannot be recovered unless you have a copy of the database elsewhere or a working backup. And if you have a working backup then use that to recover the database!
A nice little article! 🙂
Hi David,
What happens when there is no any updated backup of database? Do you think backup is the last resort to recover data from suspect mode?
As I have replied above, there is a software named Stellar Phoenix SQL database repair that can recover data from suspect database if there is no any backup of database.
Hi Mark,
If there are no backups that match the RTP/RTO of the database then I would say that somebody in the organsiation is not meeting the expectations of the business and should be shown the door.
I think restoring a backup is the best solution to a suspect database because the database was in a known working state and the backup should have been verified. The backups should also be taken frequently enough to meet the business expectations of data loss in the case of a significant failure (RTP).
I’ll certainly be looking into that software and how it works, however I am very cautious about using third party tools for data recovery because it may make decisions on what data can be lost without informing the user. I’ll probably download a trial and use a hex editor to “corrupt” one of my data files to test it.
Hi David,
Thanks for your response!!!
If you have finished the testing of the software then please share the result.