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:

1.  Files associated to the database are missing or corrupt.
2.  SQL Server crashed during the middle of a transaction.
3.  Improper SQL Server shutdown and transaction log file corruption.
4.  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:

1.  EXEC sp_resetstatus databasename;
2.  ALTER DATABASE databasename SET EMERGENCY
3.  DBCC checkdb(databasename)
4.  ALTER DATABASE databasename SET SINGLE_USER WITH ROLLBACK IMMEDIATE
5.  DBCC CheckDB (databasename, REPAIR_ALLOW_DATA_LOSS)
6.  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.

The post Database Stuck in Suspect Mode appeared first on VitaminDBA.