SQL Server Database in Suspect mode

I’m going to preface by saying, I ran into a specific scenario and I strongly suggest you see if this scenario is what’s currently affecting you before you try these troubleshooting steps.

Scenario and problem

Recently, a database of mine was placed into Suspect mode. This database is part of a two-node Availability Group. The primary problem was that the Transaction Log drive filled up on space, this caused the DB to be placed into suspect mode. Once the DB was in suspect mode, my backups began failing. (Because the DB was inaccessible and caused the job to fail.)

The reason it went into Suspect mode is because the Transaction log filled up and it was no longer able to write to it. This can cause the database to fall into Suspect mode.

Because there was no corruption or data loss, this is how I fixed getting the DB out of suspect mode in an Availability Group due to the Transaction Log filling up.

Resolution

I started by navigating to the availability group and removing the database from it, I did not want that DB to be a replica in the AG.

Afterward, I set it to emergency mode, ran a DBCC CHECKDB which indicated no errors, and to get it out of Emergency mode, I set the DB OFFLINE and then set it back to ONLINE. When I ran the OFFLINE / ONLINE in a single SSMS window, it didn’t seem to do anything after several minutes. I canceled the action and ran each one individually and it went on without a hitch. Here are the commands:

ALTER DATABASE [DATABASEName] SET EMERGENCY;

DBCC CHECKDB ([DATABASEName]);

ALTER DATABASE [DATABASEName] SET OFFLINE;

GO

ALTER DATABASE [DATABASEName] SET ONLINE;

GO;

Once this was done, I needed space on my Transaction log drive and I needed space on my secondary. Because the primary was fine and had no errors, I dropped the database from Node 2. Now when I went to shrink the Transaction Log, it was refusing to let go of space. So I had to take a full backup of the database on Node 1, then I took a T-Log backup on Node 1 for that database. Now it allowed me to shrink the file back down.

Once this was complete, I was able to add the database back into the availability group. This restored the access to the database.

Here are some articles I found that helped me out:

https://dba.stackexchange.com/questions/175844/how-to-repair-suspect-db

https://support.managed.com/kb/a398/how-to-repair-a-suspect-database-in-mssql.aspx

https://www.sqlskills.com/blogs/paul/creating-detaching-re-attaching-and-fixing-a-suspect-database/