Microsoft SQL Server does not need a Microsoft distributed transaction coordinator (MS-DTC) to work. However, applications can use it without the database administrator’s (DBA’s) knowledge. In most cases, it is no harm.
DBA’s don’t need to know. Right? Wrong! If your application is using DTC, make sure DBA knows about it. Why? Organizations are using AlwaysOn Availability Groups more and more now. So what happens to a DTC transaction that was started on the primary replica, but never finished before failover to secondary?
Hint! It becomes orphaned! Yeah! Right? Nope! No one likes being an orphan, and in the SQL Server world, the orphaned transaction also means corruption!
In a previous article, HELP! I HAVE -2, -3, or -4 SESSION ID!, I talked about how to kill these transactions that are orphaned. But that can only happen if the database is still online.
But in an Availability group, it will be offline, as blocked by Ryan Adams in MSDTC Best Practices with an Availability Group, if you do not have DTC configured with high availability, your database most likely is marked suspect. So my previous article doesn’t help. Below is the error you might see.
Microsoft Distributed Transaction Coordinator (MS DTC) failed to reenlist citing that the database RMID does not match the RMID [caa9b4d7-1fbc-45fa-9813-c3c0eede5391] associated with the transaction. Please manually resolve the transaction.
SQL Server detected a DTC/KTM in-doubt transaction with UOW {4018E86E-3673-44FF-ABA6-2EC8F04F5FD2}.Please resolve it following the guideline for Troubleshooting DTC Transaction.
Database ? cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.
And this happens because of hidden setting that, really, I never talk about. I’ll have to remember this going forward. “in-doubt xact resolution”, this setting default setting is zero. Meaning “No presumption. Recovery fails if MS DTC cannot resolve any in-doubt transaction.” (a.k.a. mark the database suspect). Aha! So the next statement might be, “Microsoft is to blame for my corruption!” That is a dumb setting!
I don’t know the developer’s thoughts, but I feel this setting, Microsoft is not making any assumptions. If you think assumptions work, then you can choose one (commit) or two (abort). But a better way to deal with this would follow Ryan’s advice, setup MS-DTC correctly for high-availability. Do yourself a favor let DBAs know if your application is using DTC.
DBA’s can check if DTC is being used by looking at sys.dm_tran_active_transactions, search for transactions with a transaction type of 4.
SQL Canada (SQLCAN) 