Troubleshooting SQL Server Distributed Transactions (Part 1 of 2)

Problem

Distributed transactions are not working in our environment. When we run a T-SQL query using “begin distributed transaction” it fails with the following error: “MSDTC on the server is unavailable” however it looks like the MSDTC service is online and working fine. In this tip, I cover a few ways to test whether MSDTC is working properly.

Solution

This tip is aimed to help the community to troubleshoot SQL and MSDTC (Microsoft Distributed Transaction Coordinator). Before I continue with this article, I would like to encourage everyone who has had specific issues resolved by Microsoft Premier Support to post it on MSSQLTips.com as a way to help each other in our daily work and troubleshooting activities.

This is the first time in my DBA career that I came across an apparent communication issue between SQL Server and MSDTC on a brand new Windows 2003 Enterprise Server and on a brand new install of SQL Server 2008. In order to resolve the issue Microsoft Premier Support was contacted.

Below is the description of the error we were experiencing anytime a begin distributed transaction T-SQL command was issued:

Message: The Microsoft Distributed Transaction Coordinator (MS DTC) service could not be contacted. If you would like distributed transaction functionality, please start this service.

When I looked at the services, DTC was running fine.

In this first part of this tip series, I will focus on all the debugging efforts provided by the Windows Support team, while in the second part I will focus on the efforts and solutions provided by the SQL Server Support team.

The following steps were taken with the Windows OS Support team in order to make sure that DTC service was working fine.

Step 1: Uninstall and Install MS DTC

1. From Add/Remove Programs, Add/Remove Windows Components under the Server Details uncheck “Enable network DTC access” services and then “Next” through the wizard until completed
2. From a command prompt run “msdtc -uninstall” without the quotes
3. Verify the DTC was uninstalled successfully by reviewing the application event log
4. Open regedit and ensure the following registry keys have been deleted (they all may not be present). Do not forget to export HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC key before deleting it in case there is any need of previous configurations, especially if there are manually added items to the XADLL key.
   • HKEY_CLASSES_ROOT\CID
   • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC
   • HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSDTC
5. Reboot the machine
6. From a command prompt run “msdtc -install” without the quotes
7. Verify the MSDTC was installed successfully by reviewing the application event log
8. From a command prompt run “msdtc -resetlog” without the quotes
9. From Add/Remove Programs, Add/Remove Windows Components under the Application Server Details check “Enable network DTC access” services and then “Next” through the wizard until completed
10. In the Administrative tools, Services verify the Distributed Transaction Coordinator service is started and then changes the properties of the service, so the startup type is “automatic”.
11. Any RPC registry configuration is removed during the re-installation, so you will need to reconfigure the server for a firewall or XA transactions if it was required..

Unfortunately, the above attempt did not fix our problem and SQL was still unable to execute a Begin Distributed Transaction correctly.

Step 2: Test DTC service for firewall issue

DTCPing utility was used to verify that there were no firewall issues. It can be downloaded from: http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=5e325025-4dcd-4658-a549-1d549ac17644

DTCPing is basically used to make sure that there are no firewall rules in place that would prevent DTC from working. This utility needs to be installed on the target machine (where DTC is not working), and on another server within the same network.

1. Start DTCPing on both servers
2. On the partner server type the name of the server where DTC is not working and click ping. In our environment, the DTCPing utility worked fine; that validated that there were no firewall issue between our servers. The DTCPing.exe tool performs the following functions:
   1. Tests name resolution – because each Microsoft Distributed Transaction Coordinator (MS DTC) service uses the NetBIOS name of its peer to talk to other MS DTC services, name resolution is the first step toward MS DTC troubleshooting. The DTCPing.exe tool first tries to resolve the computer network name to the IP address. DTCPing.exe uses this IP address to obtain the host name.
   2. Tests Remote Procedure Call (RPC) communications – RPC is the basis for MS DTC communication. The DTCPing.exe tool uses the dynamic port with Transmission Control Protocol/Internet Protocol (TCP/IP) protocol to test RPC communication. MSDTC requires successful, two-way RPC communication to function. The DTCPing.exe tool also reports more details if the host computer is running out of the TCP/IP port.
   3. Detects and display MS DTC settings – the DTCPing.exe tool displays all registry key settings that are touched by MS DTC. These settings include the Security setting that is new to the Microsoft Windows Server 2003 operating system.
   4. Tests DTC communications – MS DTC is based on two-way RPC and uses MS DTC Connection Manager protocol to establish connections. The DTCPing tool simulates MSDTC at the Connection Manager level.

Step 3: WinRM & RMclient Tools

The above tools were provide by Microsoft support and I am not sure if they are available online. The WinRM tool will run a dummy transaction between the two DTC services on the two systems. It will verify the security configuration and that the DTC service is functioning properly. To execute the tool copy the WinRM.exe to the SQL Server and the RMclient.exe to the client box that initiates the transaction. Then run WinRM.exe on the server.

Once the message “resource manager is ready” is displayed open a command prompt on the client box and navigate to the folder that contains the RMclient.exe. Then run the following command:

RMclient.exe /s <SQL Server Machine Name> /t commit

This test was successful on my server.

Step 4: DTCTester Tool

DTCTester tests a distributed transaction against a specified Microsoft SQL Server. This tool helps to test distributed transactions across firewalls or against networks. DTCTester performs distributed transactions by using ODBC API against a SQL Server database.

In order to use DTCTester, you need to create a connection to SQL Server by using a data source name (DSN)

And from the command prompt type:

dtctester SQL database-username password

Once again, the test was successful. You can see that DTCTester was able to create a temp table #dtc25254, insert some data and commit the DTC transaction.

Conclusion

After testing DTC using DTCPing, WinRM\RMclient, and DTCTester Microsoft Support concluded that the MSDTC service was running fine and that the investigation should be conducted by the SQL Server team.

Next Steps

• The next article will address DTC investigation and solutions addressed by the SQL Server Premier Support group.