Have you ever encountered a situation where your SQL Server database goes into a SUSPECT state? If so, you’re not alone. This is a common issue that many database administrators face. In this article, we will discuss how to detect and resolve a SUSPECT state in SQL Server.

Recently, one of my clients contacted me with a similar issue. They were using Microsoft SQL Server 2008 R2 in a clustered environment and had a hard failure of the SQL cluster. After reinstalling the Distributed Transaction Coordinator (DTC) component, their production database ended up in a SUSPECT state.

When I checked the SQL Server Management Studio (SSMS), I found that the database was indeed in a SUSPECT state. The error log indicated a DTC/KTM in-doubt transaction with a unique identifier. The error messages suggested troubleshooting DTC transactions, but there were no transactions listed in the DTC.

The solution to this issue was to change a setting in SQL Server that would abort the in-doubt transactions and allow the recovery process to continue. This can be done by executing the following commands:

sp_configure 'in-doubt xact resolution', 2
GO
RECONFIGURE
GO

The value 1 or 2 determines what action should be taken with the in-doubt transactions. In this case, since the transactions would have been rolled back on another server, we changed the value to 2. After restarting the SQL resource, the database came back online.

If you encounter a SUSPECT database, it is important to diagnose the recovery errors and fix them, or restore from a known good backup. If the errors persist or if you need further assistance, it is recommended to contact technical support.

As a database administrator, it is crucial to be familiar with troubleshooting SUSPECT databases. If you have encountered a SUSPECT database before, please share the reason and the fix in the comments below to help others facing similar issues.