Troubleshooting Microsoft Distributed Transaction Coordinator (MSDTC / DTC)

Microsoft Distributed Transaction Coordinator (abbreviated to MSDTC, or DTC) is usually very quick and simple to set up. However, it isn't the most intuitive thing to troubleshoot if you run into issues. This page outlines various tools that can be used to assist in troubleshooting MSDTC, and how to use them.

What is MSDTC?

MSDTC is the software that allows "distributed transactions" to run across multiple servers. A distributed transaction is something that updates data on two or more servers. MSDTC ensures that updates on all servers succeeed, or if there is a problem with ANY of the updates, then all of the updates are rolled back.

For example, if you wanted to move data from Server A to Server B, you could do this in a distributed transaction. The data would be written to Server B, and deleted from Server A simeltaneously. If there was a problem with the write to Server B, or the deletion from Server A, the transaction would fail, rolling back both the write and the deletion, leaving both servers exactly as they were. In this example, this ensures there is no loss of data (if only the deletion of data from Server A succeeded), and no duplication of data (if only the write to Server B succeeded).

DTC uses Port 135, and the DCOM port range, which is 1024 - 65535. As this is such a large range of ports to open, the DCOM port range can be limited in the registry if required

How to Enable and Configure MSDTC

1. Navigate to Control Panel > Administrative Tools > Component Services
2. Within Component Services, expand Component Services > Computers > My Computer > Distributed Transaction Coordinator. Here you'll see Local DTC, or, if you're on a cluster, you'll also see Clustered DTC
3. Right click the Local DTC (or Clustered DTC if required), and click Properties
4. Navigate to the Security tab
5. Select the following options as a default:
   
   Network DTC Access
   Allow Inbound
   Allow Outbound
   No Authentication Required (set by default)
   Enable SNA LU 6.2 Transactions (set by default)
   Account NT Authority\NetworkService (set by default)
   
   Note, Enable XA Transactions may also be required for SQL Server

It really is that simple enabling DTC, and typically, this is all that needs to be done to allow two servers to run distributed transactions between them.

There is more information about each DTC setting here: https://technet.microsoft.com/en-us/library/cc753620(v=ws.10).aspx

Test DTC is Working Between Two SQL Servers

It is relatively simple to test whether or not DTC is working between two SQL servers.

1. On Server A, set up a linked server to Server B. On Server B, create a test table. Ensure the linked server account on Server A has permission to write to the test table on Server B
2. On Server A, run the following SQL, replacing the table name etc with a test table:

set xact_abort on

begin distributed transaction

insert into ServerB.DBName.dbo.testTable (ID) values (1)

commit transaction

If it has committed successfully, SQL Server Management Studio will return (1 row(s) affected). If it fails, there may be DTC connectivity issues. The following tools may prove helpful troubleshooting.

DTCPing

DTCPing is a tool that allows testing DTC connectivity between two servers.

How to Use DTCPing

DTCPing must be installed on both servers you wish to test connectivity issues between. By default, the DTCPing installation installs to the C:\Windows\Temp folder (or subfolder buried in the temp directory somewhere). It's usually helpful to move the files to C:\DTCPing for ease of use.

1. Once DTCPing has been installed on both servers, open the application on both servers
2. On SOURCE server, enter DESTINATION server NETBIOS name into the Remote Server Name field
3. On DESTINATION server, enter SOURCE server NETBIOS name into the Remote Server Name field
4. Click PING on SOURCE server
5. Click PING on DESTINATION server

If a successful test has happened, the output will read similar to the following:

++++++++++++++++++++++++++++++++++++++++++++++
DTCping 1.9 Report for SOURCE
++++++++++++++++++++++++++++++++++++++++++++++
RPC server is ready
++++++++++++Validating Remote Computer Name++++++++++++
11-21, 04:31:01.455–>Start DTC connection test
Name Resolution:
DESTINATION–>65.52.22.254–>DESTINATION.contoso.com11-21, 04:31:01.470–>Start RPC test (SOURCE–>DESTINATION)
RPC test is successful
Partner’s CID:084B708C-F0C5-4E65-95F2-8E2DEF73FFF3
++++++++++++RPC test completed+++++++++++++++
++++++++++++Start DTC Binding Test +++++++++++++
Trying Bind to DESTINATION
11-21, 04:31:01.830–>SOURCE Initiating DTC Binding Test….
Test Guid:B5544E05-D64B-40AC-B283-71947914DED3
Received reverse bind call from DESTINATION
Network Name: SOURCE
Source Port: 1116
Hosting Machine:SOURCE
Binding success: SOURCE–>DESTINATION
++++++++++++DTC Binding Test END+++++++++++++

Should any errors be reported, the following link describes common errors and their resolutions: https://blogs.msdn.microsoft.com/puneetgupta/2008/11/12/troubleshooting-msdtc-issues-with-the-dtcping-tool/

DTCPing outputs logs to the folder the DTCPing.exe has been installed to, and also includes various .txt files explaining the DTCPing log format.

DTCTester

Next up is DTCTester, which only tests DTC transactions one way, from a source server to a destination server. It is command line only.

How to Use DTCTester

1. Install DTCTester on the server you want to test distributed transactions from. Install to C:\DTCTester or somewhere helpful!
2. Create an ODBC connection on the source server, to the destination server
3. Navigate to Administrative Tools > Data Sources (ODBC)
4. On the User DSN tab, click Add to add a new ODBC connection
5. Select SQL Server
6. Enter details for the destination SQL Server you wish to test DTC for
7. Run cmd.exe as an administrator
8. Navigate to the folder where you installed dtctester.exe (type cd c:\DTCTester)
9. Next, run dtctester <ODBC Name> <Username> <Password> where
   <ODBC Name> is the name of the ODBC connection you set up in step 1
   <Username> is the name of a sysadmin user
   <Password> is the corresponding sysadmin password

DTCTester will then attempt to create, and write to, a temporary table on the destination server using a distributed transaction. A successful output looks as follows:

Command Line: dtctester test sa
Executed: dtctester
DSN: test
User Name: sa
Password is assumed to be NULL.
Connecting to the database
tablename= #dtc7488
Creating Temp Table for Testing: #dtc7488
Warning: No Columns in Result Set From Executing: 'create table #dtc7488 (ival int)'
Initializing DTC
Beginning DTC Transaction
Enlisting Connection in Transaction
Executing SQL Statement in DTC Transaction
Inserting into Temp...insert into #dtc7488 values (1)
Warning: No Columns in Result Set From Executing: 'insert into #dtc7488 values (1) '
Verifying Insert into Temp...select * from #dtc7488 (should be 1): 1
Press enter to commit transaction.

Committing DTC Transaction
Releasing DTC Interface Pointers
Successfully Released pTransaction Pointer.
Disconnecting from Database and Cleaning up Handles

More information on DTCTester here, including typical errors: https://support.microsoft.com/en-gb/kb/293799

Tracing DTC Output

It is possible to stop and start logging of DTC output to help understand what is actually happening when enlisting distributed transactions.

Setting up DTC Tracing

DTC tracing can be configured from the Tracing tab of the DTC Properties (see How to Read DTC Trace Files below)

1. Navigate to Control Panel > Administrative Tools > Component Services
2. Within Component Services, expand Component Services > Computers > My Computer > Distributed Transaction Coordinator. Here you'll see Local DTC, or, if you're on a cluster, you'll also see Clustered DTC
3. Right click the Local DTC (or Clustered DTC if required), and click Properties
4. Navigate to the Logging tab to see the Location value where DTC trace data will be saved to (by default, it is C:\Windows\system32\Msdtc\Trace)
5. Navigate to the Tracing tab. Ensure Trace Output and Trace Transactions is selected. Also ensure types of transactions you wish to trace are selected
6. Click Stop Session, then New Session to begin a new DTC trace. You can now test your DTC connectivity. Once finished, click Stop Session to end tracing of DTC
7. Navigate to the folder selected in step 4 to find the trace files (see How to Read DTC Trace Files below)

How to Read DTC Trace Files

Frustratingly, DTC trace files are written in binary, and are indeciperable to the average human eyeball. Microsoft do not make it easy to read these files. The software required to open the files is not installed with Windows. Again, this is command line only.

1. Copy tracefmt.exe to the directory that DTC trace files are output to (by default it is C:\Windows\system32\Msdtc\Trace). Note, you cannot paste this path into explorer to get to this folder! But you can navigate to it manually
2. Run cmd.exe and navigate to the trace output folder (by default it is C:\Windows\system32\Msdtc\Trace) by running the command cd C:\Windows\system32\Msdtc\Trace
3. Next, run the command msdtcvtr.bat -tracelog <tracefile name> which will output the trace file contents to trace.csv in the same folder

There is more info on reading trace files, and where to get tracefmt.exe here: http://stackoverflow.com/questions/1329583/where-do-i-get-tracefmt-exe-and-how-do-i-read-my-msdtc-traces