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.
-- 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);
-- 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 🙂