My holiday week did not started off very good. I come in and find a UAT environment database in suspect mode. Having a database in suspect mode means a few different things could have happened. Some of the reasons why a databases goes to suspect mode are:
- Files associated to the database are missing or corrupt.
- SQL Server crashed during the middle of a transaction.
- Improper SQL Server shutdown and transaction log file corruption.
- In some cases 3rd party software that works with the underlying database files could cause suspect mode.
In my case with this UAT database, I believe an improper SQL Server shutdown causes some transaction log file corruption. This particular database is a part of an availability group and I am leading to believe that during the regular sever shutdown maintenance window I had some active transactions occurring, this paired with the shutdown likely causes some corruption.
Now to the fun part, fixing this database. I had two ways of doing this (luckily I kept a good backup strategy). The 1st way is to try and repair it, the 2nd way would be using a good backup to restore from.
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 the “emergency” mode and allow you to attempt a repair. In my case this repair failed with the following error:
Rebuilding log is not supported for databases containing files belonging to MEMORY_OPTIMIZED_DATA filegroup
The emergency-mode repair failed.You must restore from backup.
If the emergency repair fails you can always restore the database from a previous backup, which is what i ended up doing to resolve this issue. Hopefully this provides some value to someone if they encounter a database in suspect mode.