Microsoft Distributed Transaction Coordinator, abbreviated MSDTC or just DTC, is a somewhat aged but still very essential technology from Microsoft.
As a SQL Server DBA I have primarily met MSDTC under the Linked Server functionality. Here MSDTC serves the technical transactions between the SQL Server installation where the linked server is defined and the target of the linked server on another Windows Server. The MSDTC usage is implicit when using a linked server, primarily as the linked server is based on the OLEDB technology.
As a side note this is why you will often see OLEDB waits in SQL Server when working over a linked server.
Graphical User Interface
The primary GUI for MSDTC is the – also – elder Microsoft Management Console (MMC) component named "Component Services". This is a COM+ snap-in for MMC. If you are using a Danish Windows then the component is named "Komponenttjenester". It is not easy to find in-depth documentation on COM+ or MMC, and the voluminous (five-volume) "COM+ Developers´s Reference Library" is unfortunately no longer available in the book stores, that is either as new or used.
Usually you are working on the local MSDTC. You might find the path in Component Services long, but here it is:
- Console Root
- Component Services
- Computers
- My Computer
- Distributed Transaction Coordinator
- Local DTC
When you right-click Local DTC you have access to the properties of the Local DTC with three configuration areas in separate tabs. The first tab is on MSDTC tracing configuration.
The second tab is on MSDTC logging configuration.
The third and last tab is on MSDTC security configuration.
All the examples are with default values.
When expanding Local DTC you see two items:
- Transaction List
- Transaction Statistics
The first item Transaction List will give you a simple list of active transactions. But there is not much more you can see on each transaction or the transaction history.
The second item Transaction Statistics give you a rather simple graphical presentation of a few measures. There are no possibilities to drill-down or get other other details.
I think that the limited monitoring shows the age of the MSDTC component.
MSDTC Log Files
Is a standard Windows installation the MSDTC log files are in
C:\Windows\System32\MsDtc
The log files are in a proprietary binary Microsoft format, with no local tools to read the log files.
You can open the MSDTC log files with the command line tool tracefmt.exe. But you will have to download Windows SDK to get this tool. With Windows SDK you will get tracefmt.exe in both 32- and 64-bit editions in these (default) locations:
C:\Program Files (x86)\Windows Kits\10\bin\10.0.22621.0\x64C:\Program Files (x86)\Windows Kits\10\bin\10.0.22621.0\x86
The paths are examples from the current Windows SDK version.
Using tracefmt.exe is not complicated but also not just point-and-click. Read the documentation and spend some time with the syntax to get a output you can use.
MSDTC Application Error
Some errors will surface in Windows Application Event Log like this example.
Log Name: Application
Source: Microsoft-Windows-MSDTC Client 2
Date: 28-11-2022 07:34:19
Event ID: 4879
Task Category: CM
Level: Warning
Keywords: Classic
User: N/A
Computer: SQLDB42.sqladmin.lan
Description:
MSDTC encountered an error (HR=0x80000171) while attempting to establish a secure connection with system SQLDB666.
MSDTC in AlwaysOn Availability Groups
As SQL Server AlwaysOn Availability Groups are based on Windows Failover Cluster, among others, you will have to go through some configuration details to get a robust transaction even during a failover. There are some basic documentation like Configure distributed transactions for an AlwaysOn availability group, but I really think you will have to do some more reading, build a sandbox and try several solutions thoroughly before going live.
This could be important if you are working wrestling with an application build with a high-level framework like .NET Entity Framework or Java Hibernate, as such frameworks tend to use client-side transactions and then rely on distributed transactions.
Posts
