Troubleshooting MS DTC Transactions
A distributed transaction is in-doubt when the outcome of the transaction cannot be determined. This occurs when the transaction in Microsoft® SQL Server™ was prepared by Microsoft Distributed Transaction Coordinator (MS DTC), but one or more of the MS DTC processes involved in the transaction is not reachable from SQL Server.
A SQL Server user can detect this situation when the following message appears in the error log:
<SQL Server detected a DTC in-doubt transaction for UOW <xxx>. Please resolve it following the guideline for Troubleshooting DTC Transactions.>
To correct this problem, identify the MS DTC processes involved in the transaction and either start or restore the connection to the processes. Under typical circumstances, this procedure should fix the situation and the transaction should resolve itself. If this step does not resolve the issue, consult the MS DTC user manual for specific information on dealing with in-doubt transactions.
SQL Server can resolve in-doubt transactions; however, this option should be used only in extreme cases – when the MS DTC process required for in-doubt transaction cannot solve the problem.
With the in-doubt transaction's Unit of Work ID (UOW) accessible, users can resolve the transaction in SQL Server by using a specific KILL command syntax, and then performing the steps to recover the database. For information, see Backing Up and Restoring Databases.
Syntax
KILL UOW WITH { COMMIT | ROLLBACK }
Arguments
UOW
Identifies the Unit of Work ID representing the in-doubt MS DTC transaction. UOW is a character string and can be obtained from the SQL Server error log. For more information about monitoring distributed transactions, see the MS DTC user manual.
WITH { COMMIT | ROLLBACK }
Specifies that the Unit of Work ID identified should be either committed or stopped. An MS DTC transaction can be forced to commit or stop only if the transaction is in the prepared state, and thus not resolved, and MS DTC is in recovery. MS DTC is said to be in recovery when it is not accessible or when SQL Server is recovering from a failure.
This KILL syntax can be used only to resolve in-doubt transactions that are in the prepared state. For information about terminating other distributed transactions, see KILL.
Important Use the KILL command with the UOW WITH COMMIT or ABORT option only in extreme cases when the MS DTC utilities are not accessible.