Power Line Distribution

MSDTC Troubleshooting – Basic Guide

If you haven’t worked with MSDTC before you might mistake it for a simple straight forward service which is easy to setup, configure, and troubleshoot. I too once thought that until I stumbled into the thick forest of MSDTC with those promises and didn’t return for years all grizzly and worn (ok it wasn’t that bad).

What comes next is my contribution to those who follow into the dark denizens of MSDTC hoping to fix things so they can go home.

What is MSDTC and Why Do We Care?

MSDTC is a distributed transaction coordinator created by Microsoft. A frequent use of MSDTC is on a SQL Failover Cluster. Much of what is written below can be directly applied to this situation.

MSDTC comes with Windows by default – in the sense that you don’t need to install any roles or features to get it. The most basic function it performs is to keep transactions ACID. The most popular implementation of this is 2 phase commit. The transaction manager makes sure all is well and compliant with ACID for the transaction to commit and then allows it to commit.

MSDTC Background

MSDTC Overview

Microsoft Distributed Transaction Coordinator (MSDTC) is a Windows service that coordinates transactions spanning multiple databases. It is a transaction manager that allows applications to include several different sources of data in one transaction. MSDTC coordinates committing the distributed transaction across all servers enlisted in the transaction.

An example here would be a process on one machine calling a stored procedure on another machine which in requires data that changes together in a transaction.

The MSDTC service is running on each of the servers to manage the successful commit (or rollback) of the transaction across all servers enlisted in the transaction.

If distributed transactions are being used with SQL Server then it is required that MSDTC be installed and used for distributed queries, two-phase commit transactions, and some replication functionality.

Here is another example to show how it integrates with an Application Server forming a distributed transaction with SQL Server:

MSDTC General Flow

The MSDTC Service

It looks like this and it should appear on every machine which will participate in a distributed transaction.

DTC service

DTC service properties

The service startup type should be Automatic or Automatic (Delayed Start)

MSDTC Installation / Reinstallation

MSDTC needs to be installed, configured, and running on each host that contains a SQL instance. It must work bi-directional from each host to the other.

Let’s see now how to install.

How To Re-Install MSDTC

The service will need to be uninstalled, the server rebooted, then installed.

You need to perform the above operating from the command line running as an administrator!

  1. Stop the service:
  2. Uninstall the service:
  3. In the registry, remove the following keys if they exist:
    1. HKEY_CLASSES_ROOT\CID
    2. HKEY_CLASSES_ROOT\CID.Local for Windows 2008
    3. HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSDTC
    4. HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\MSDTC
    5. HKEY_LOCAL_MACHINE\SYSTEM\ControlSet002\Services\MSDTC
    6. HKEY_LOCAL_MACHINE\Software\Microsoft\MSDTC
  4. Reboot the server
  5. Install the service:
  6. Check the Event Viewer (Windows Application Logs) for the following message:
  7. Run:
  8. Start the service:
  9. Check the msdtc configuration and correct if necessary

When making changes to the registry it is a good practice to first take a backup of the registry!

How to reinstall MS DTC for a nonclustered Windows 2000 Server

MSDTC Configuration

Remember that it needs to be installed, configured, and running on each host that contains a SQL instance for our products.

It must work bi-directional from each host!

i.e. Server A > Server B and Server B to Server A

Let’s see how to configure it now.

How to Configure MSDTC

On each server the service runs and can be configured via Component Services:

  1. Open Component Services
    1. Click Start > Administrative Tools > Component Services


    NOTE: …or perform this via the command line – “dcomcnfg”

  2. Expand Component Services
  3. Go to Computers > My Computer > Distributed Transaction Coordinator > Local DTC
  4. Right click on Local DTC > Properties > Security Tab
  5. On this tab choose the following options
    1. Network DTC Access
    2. Allow Remote Clients
    3. Allow Remote Administration
    4. Allow Inbound
    5. Allow Outbound
    6. No Authentication Required
    7. Enable SNA LU 6.2 Transactions
    8. DTC Logon Account: NT Authority\Network Service

    Local DTC Properties

  6. Click OK
  7. Restart the service

Testing MSDTC

This simple query can be used to test the service:

If no error is listed in the output window then it handled the query just fine.

NOTE: the other SQL instance must be on a different machine!

Validating Linked Server Configuration

The above query tested a remote query. This is possible because a linked server has been setup to connect to the remote SQL Instance. There are a few things we should confirm about the linked server connection:

  1. Test the connection to the linked server – right click on the linked server > Test Connection
    Linked Server Test Connection
  2. Verify security settings – under the Security tab make sure the radio button is selected for “Be made using the login’s current security context”
    Linked Server Properties
  3. Set the Server Options on the linked server – right click on the linked server > Properties
    Linked Server Options

    1. Set RPC, RPC Out, and Enabled Promotion of Distributed Transactions to TRUE
    2. Alternatively run the following SQL:

    Reading the MSDTC Log

    The log is located by default at: %windir%\System32\Msdtc

    NOTE: Open this via command prompt using admin privileges! Do not try to access via file explorer.

    The logs are in binary and are not viewable by a text editor such as notepad. You’ll need to view the data a different way. Here’s an example of how to view the trace data:

    • Using command prompt with elevated admin rights, type:
    • msdtcvtr.bat converts the binary trace file to human readable text
    • Default location is: C:\Windows\System32\Msdtc\Trace
    • You must include “tracefmt.exe” in the path when running msdtcvtr.bat
        • Find this in the Windows Driver Kit (WDK) – See Download Windows Drivers Kit
        • Download, unpack, and install full development environment
        • Search for “tracefmt”, “msdtcctr.mof”, “msdtcvtr”, “traceprt.dll” and put all files in the directory of the trace log to be read

    • See view trace data in references for more details

     


     

    What’s next? Now that some of the basics have been covered we are ready to discuss some more common and specific failures which are apt to occur. See part 2 here!

3 thoughts on “MSDTC Troubleshooting – Basic Guide

Leave a Comment