Written by Over the many years I’ve been using the various different versions of Microsoft SQL Server there’s an error that I’ve only ever seen once, a long time ago, and never since. It was the dreaded database in ‘suspect’ mode (see here for a list of database modes).
After a few moments of bewilderment, face pulling and a cry of whaaaat the…? I headed off to the internet, as we all do, to see what on earth it meant – apart from the obvious meaning that something was ‘suspect’ of course…
According to Microsoft, this particular error means:-
At least the primary filegroup is suspect and may be damaged. The database cannot be recovered during startup of SQL Server. The database is unavailable. Additional action by the user is required to resolve the problem.
Oh s**t, what the? why? This is a bl**dy SQL Server cluster with a SAN server, how has this happened?? It was not something I was expecting to see on the setup I was working with at that time, which I thought was pretty decent…
Somehow, and I’m still not sure how, we managed to fix it with no issues. Although I’m sure others aren’t so lucky as we were. I was fully expecting that we’d have to restore from last backup. We tried various fixes like trying to take it offline/online. I luckily resisted the temptation to detach the database as apparently that is not a good idea I’ve learned since.
At the time we used the sp_resetstatus stored procedure, however that appears to now be on the list for deprecation, then set the database to emergency mode, followed by a DBCC CHECKDB command…
-- reset status
EXEC sp_resetstatus 'NAME_OF_SUSPECT_DB';
-- set emergency mode
ALTER DATABASE NAME_OF_SUSPECT_DB SET EMERGENCY;
-- perform integrity check
DBCC CHECKDB(NAME_OF_SUSPECT_DB);
Unfortunately it didn’t end there, we had to then set the database back into SINGLE_USER mode and run another CHECKDB with the repair option, lastly setting the database back to MULTI_USER mode:-
-- put into single user mode
ALTER DATABASE NAME_OF_SUSPECT_DB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
-- try and repair errors
DBCC CHECKDB(NAME_OF_SUSPECT_DB, REPAIR_ALLOW_DATA_LOSS)
-- back to normal ;-)
ALTER DATABASE NAME_OF_SUSPECT_DB SET MULTI_USER
In our case this fixed the problem and to this day I’ve no idea why it actually happened. It’s something I’ve never seen again and hopefully never will. Take care of your databases and they’ll take care of you 🙂