What is MSDTC?
MSDTC is a acronynm for Microsoft Distributed Transaction Coordinator which is a windows service to maintain the transactions in a distributed environment. The internals of MSDTC is a black box for users, however, this service is very important to maintain the transactions between two different systems. A Distributed transaction is a transaction that spans across multiple machines.Distributed transaction will ensure if there are multi operation happens between servers, if any of the operation is failed and cancelled, none of the operations in the transaction is getting committed in a multi server environment. I am not sure if I can simply explain than this.
Where do we need MSDTC?
Typically, in the context of SQL Server, yes we may need MSDTC for Linked Server usages.If transaction is opened to do some operations across servers in a linked server environment, this needs to be converted as a distributed transaction to maintain the integrity of the data. We need to understand two things clearly here.
1. When two physical machines are communicating with a transaction. If you are using two instances of the same machine, DTC will not be used. However, if the instances are in a cluster, you need to use DTC as you cannot ensure the instances are in the same physical nodes in the cluster environment.
2. Irrespective of type of transaction, whether its implicit or explicit, you need to use DTC. A typical example for implicit transaction is Trigger. Please find more detail here.
How to troubleshoot MSDTC issues?
Troubleshooting MSDTC issues is a pain area as you will not have complete information about the DTC component. We can use DTCPing tool to troubleshoot the issues which will provide much better insights.
DTCPing tool can be downloaded here.
How to configure and troubleshoot using DTCPing tool?
Test Network Connectivity
1. On Computer A, run DTCPing.exe.
2. On Computer B, run DTCPing.exe.
3. On Computer A, type the NetBIOS name of Computer B, and then click Ping.
4. On Computer B, type the NetBIOS name of Computer A, and then click Ping.
NOTE: The DTCPing log file can be found in the same folder as the DTCPing.exe file. The log file name has the following format: “NetBIOSName” + “ProcessID” + .log
You can see more details in the readme.txt available in the tool kit.