Lately, I’ve been fighting with MSDTC (the Microsoft Distributed Transaction Coordinator). I have a process which runs a number of scripts (index defragmentation, DBCC checks, error log reads, etc.) for a number of servers and stores information on the host server so I can analyze the data in one place later. This was working just fine until we virtualized the host server. At that point, I started getting errors like the following:
MSDTC: OLE DB provider “SQLNCLI” for linked server “analytics” returned message “No transaction is active.”
Msg 7391, Level 16, State 2, Line 2 The operation could not be performed because OLE DB provider “SQLNCLI” for linked server “[ServerName]” was unable to begin a distributed transaction.
I looked and found some good resources for solving this problem (including one which is almost all-encompassing), but all to no avail. I figured that it couldn’t be a configuration problem because some of the servers could do distributed transactions, but others couldn’t, and there weren’t any configuration differences between the two. Eventually, as I was messing around with a number of options on the host and called servers, I checked out the event log on the called server and noticed a message in the Application log:
The local MS DTC detected that the MS DTC on POLARIS has the same unique identity as the local MS DTC. This means that the two MS DTC will not be able to communicate with each other. This problem typically occurs if one of the systems were cloned using unsupported cloning tools. MS DTC requires that the systems be cloned using supported cloning tools such as SYSPREP. Running ‘msdtc -uninstall’ and then ‘msdtc -install’ from the command prompt will fix the problem. Note: Running ‘msdtc -uninstall’ will result in the system losing all MS DTC configuration information.
What happened in our case was that the servers were spun up from the same VMware template, so MS DTC had a conflict. Solving this was pretty easy. Run the following commands in the command prompt (as an Administrator!) for either the caller or the called server (or both):
net stop msdtc
Then restart the server—not just the MSDTC service—and you’re good to go.