How to fix Error 8566 on Windows Server 2025 / Windows 11

by

Troubleshooting Guide: SQL Server Error 8566 – Unable to Commit Prepared Transaction (MS DTC)

Why This Error Happens

Error 8566 occurs when SQL Server 2025 fails to commit a prepared (in-doubt) transaction coordinated by MS DTC, typically because the DTC service lost communication with the SQL Server resource manager mid-transaction. This is commonly caused by a corrupted or misconfigured DTC log file, network interruptions between the application server and SQL Server, or insufficient permissions on the NT AUTHORITY\NetworkService or NT AUTHORITY\SYSTEM account used by the MSDTC service. In clustered or AlwaysOn environments, this can also happen if the DTC resource did not fail over correctly alongside the SQL Server resource group.

Step-by-Step Resolution Guide

Step 1: Verify MS DTC Service Status and Configuration

  • Open Component Services (comexp.msc) on the Windows Server 2025/Windows 11 host.
  • Navigate to Component Services > Computers > My Computer > Distributed Transaction Coordinator > Local DTC.
  • Right-click Local DTC > Properties and confirm Network DTC Access is enabled under the Security tab.
  • Ensure Allow Inbound/Outbound, Mutual Authentication Required (or Incoming Caller Authentication Required for cross-domain setups), and Enable Transaction Internet Protocol (XA Transactions) are checked.
  • Restart the service via PowerShell: Restart-Service MSDTC -Force

Step 2: Resolve In-Doubt Transactions Manually

  • Open a query window in SSMS and run: KILL '<UOW_ID>' WITH TRANSACTION STATUS to inspect the transaction state.
  • Identify the UOW (Unit of Work) GUID using: SELECT * FROM sys.dm_tran_locks WHERE request_type = 'Transaction'
  • If the transaction is confirmed orphaned, manually resolve it: KILL '<UOW_ID>' WITH COMMIT or KILL '<UOW_ID>' WITH ROLLBACK depending on the known outcome of the distributed transaction.

Step 3: Rebuild or Reset the DTC Log and Permissions

  • Stop the MSDTC service: net stop msdtc
  • Reset the DTC log by running: msdtc -resetlog from an elevated Command Prompt.
  • Verify the NT AUTHORITY\NetworkService account has Full Control on C:\Windows\System32\MSDTC and C:\Windows\System32\MsDtcDoc directories.
  • Restart the service: net start msdtc
  • Restart the SQL Server instance to force MS DTC recovery to re-scan and reconcile any remaining in-doubt transactions.

Step 4: Check Firewall and Port Configuration

  • Confirm Windows Firewall allows RPC (port 135) and the dynamic RPC port range (49152–65535 by default on Windows Server 2025) for DTC traffic.
  • Run netsh advfirewall firewall show rule name="Distributed Transaction Coordinator" to confirm the built-in rule is enabled.
  • If using a hardware firewall between app servers and SQL Server, ensure both inbound and outbound RPC endpoint mapper traffic is permitted.

Alternative or Verification Steps

  • Verify the fix by querying SELECT * FROM sys.dm_tran_locks and the SQL Server Error Log (EXEC sp_readerrorlog) to confirm no further Error 8566 entries appear after a forced distributed transaction test.
  • If the issue persists, use the DTCPing tool between the application and SQL Server hosts to isolate network-level DTC communication failures, or consider migrating to the newer Microsoft Elastic Transactions support in SQL Server 2025 which reduces dependency on classic MSDTC for certain workloads.