Unable to connect to Microsoft Distributed Transaction Coordinator (MS DTC) to check the completion status of transaction

13 June,2017 by Jack Vamvas

How to recover a suspect database in SQL Server? I’ve restarted a SQL Server after an outage to discover a suspect database in SQL Server.

A quick check on the SQL Error Logs reports

Unable to connect to Microsoft Distributed Transaction Coordinator (MS DTC) to check the completion status of transaction (xxxxxxxxx). Fix MS DTC, and run recovery again.

What should I do? Currently the database is in suspect mode and I have not done anything.

 

Answer: Before you do anything check the last full backup you have in place. Although at this point you do not need to complete a recovery , it’s important to establish the last recovery point for this database. If there is no relevant backup – make a note to implement a solid backup strategy. Read about some essential steps on database corruption: 7 Essential Steps to Recover a Corrupt SQL Server Database (SQL ...

The error message you’ve reported regarding MS DTC is usually easy to fix. The application connecting to this database uses MS DTC as the transaction manager. Typically the application uses MS DTC to manage the transaction and considers the combination of the application , MS DTC and SQL Server as a Unit of Work (UOW).  

If the application is using MS DTC - you may notice increased usage of the DTC wait type - to understand the nature of this wait type read SQL WAIT TYPE – DTC and how to reduce it (SQL Server DBA)

The MSDTC waits for SQL Server to complete it’s work , SQL Server looks for a target to respond , and if the application server is not available , the transaction can sit in MS DTC as suspect.

The fix is to go into MSDTC and clean up the in-doubt transactions.   As a follow up communicate with the application owners to ensure data integrity is OK – or any other clean up tasks which may be required.

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Unable to connect to Microsoft Distributed Transaction Coordinator (MS DTC) to check the completion status of transaction


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer