Suspect Database Issue Due to MSDTC Orphan Transaction

SQLServerF1

It is not strange to see different kinds of issues after restart of SQL Server instance or reboot of a server or after failover of cluster SQL Server instances. Below is one of the issue you may encounter due to MSDTC service orphan transaction causing the database recovery to fail after a restart of the server. Below are the errors you may see in the SQL Server errorlog.

Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
The Microsoft Distributed Transaction Coordinator (MS DTC) service could not be contacted. If you would like distributed transaction functionality, please start this service.
Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
The Microsoft Distributed Transaction Coordinator (MS DTC) service could not be contacted. If you would like distributed transaction functionality, please start this service.
Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
The Microsoft Distributed Transaction Coordinator (MS DTC) service could not be contacted. If you would like distributed transaction functionality, please start this service.
SQL Server detected a DTC/KTM in-doubt transaction with UOW {995D82F2-3C9B-4249-C827-B33FC42BAD82}.Please resolve it following the guideline for Troubleshooting DTC Transactions.
Error: 3437, Severity: 21, State: 3.
An error occurred while recovering database ‘TestDB’. Unable to connect to Microsoft Distributed Transaction Coordinator (MS DTC) to check the completion status of transaction (0:-1647710395). Fix MS DTC, and run recovery again.
Error: 3414, Severity: 21, State: 2.
An error occurred during recovery, preventing the database ‘TestDB’ (database ID 6) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.

– If you run query against sys.sysdatabases, you will see that the status of the database is shows as suspect. Also suspect status is shown for the database under SSMS GUI.
– This issue happens after restart of server and SQL Server is trying to recover a database which has a incomplete transaction related to linked server or a remote query which uses MSDTC.
– Once the server is rebooted, SQL Server started and tried to start and recover all databases as usual and it found a open transaction in TestDB which was a linked server query using MSDTC, but the MSDTC service was not yet started resulting in failure to recover the transaction.
– To resolve the issue, DBA has to start the MSDTC service, if it is not yet started.
– If MSDTC service has started later, then DBA has to recover and bring the database online by running the below query
Alter database DBName SET ONLINE

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Thanks,

SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

Leave a Reply

Your email address will not be published. Required fields are marked *