Fix XACT_S_SINGLEPHASE (0x0004D009) SQL Transaction Error

You're staring at XACT_S_SINGLEPHASE (0x0004D009) and your transaction just died. I've been there—it's frustrating when SQL Server won't commit a simple distributed operation. Let's cut to the chase and get this fixed.

Quick Fix: Adjust MSDTC Security Settings

The most common cause is that MSDTC (Distributed Transaction Coordinator) isn't configured correctly. Here's what to do:

1. Open Component Services on the SQL Server machine. Press Win + R, type dcomcnfg, hit Enter.
2. In the left pane, expand Component Services → Computers → right-click My Computer → choose Properties.
3. Click the MSDTC tab.
4. Click the Security Configuration button. After clicking that, you'll see a new window with checkboxes.
5. Check these boxes:
   Network DTC Access
Allow Inbound
Allow Outbound
Enable XA Transactions
Enable SNA LU 6.2 Transactions (if you use SNA).
6. Leave Authentication Required selected with Mutual Authentication Required unless both servers are using the same domain account.
7. Click OK on all windows. You'll get a warning about restarting the MSDTC service—that's normal.
8. Restart the MSDTC service: open Services (services.msc), find Distributed Transaction Coordinator, right-click, click Restart.

After the restart, try your transaction again. If you still see the error, move to the next step.

Why This Fix Works

XACT_S_SINGLEPHASE means SQL Server asked a resource manager (like your linked server) to do a single-phase commit, but the resource manager didn't have the right DTC settings to handle it. By enabling inbound and outbound DTC access, you're allowing the transaction to flow between the two systems properly. The XA Transactions checkbox is key here—without it, some resource managers can't complete the commit phase.

Less Common Variations of the Same Issue

1. Linked Server Configuration Is Wrong

Sometimes the DTC settings are fine, but the linked server itself is misconfigured. Check these:

-- Check linked server options
EXEC sp_linkedservers;

-- For each linked server, make sure 'collation compatible' is false
-- and 'data access' is true
EXEC sp_serveroption @server = 'YourLinkedServer', @optname = 'collation compatible', @optvalue = 'false';
EXEC sp_serveroption @server = 'YourLinkedServer', @optname = 'data access', @optvalue = 'true';

Also, verify that the remote server's remote proc transaction promotion setting isn't blocking the commit. Run this on the remote server:

-- On the remote server
EXEC sp_configure 'remote proc transaction promotion', 1;
RECONFIGURE;

2. Firewall Blocking Port 135

MSDTC uses RPC dynamic port allocation, but it starts with port 135. If that's blocked between your SQL servers, you'll see this error. Open port 135 on both firewalls. Then, configure a static port range for MSDTC to make things reliable:

1. Open Component Services → My Computer → Properties → MSDTC tab.
2. Click Security Configuration.
3. Under Network DTC Access, click Settings.
4. In the Port Range field, enter a range like 5000-5100. This tells MSDTC to use only those ports.
5. Open those ports in Windows Firewall (both inbound and outbound).

3. Old SQL Server Version or Missing CU

I've seen this error more often on SQL Server 2016 before SP2 and SQL Server 2017 before CU15. If you're running an older version, apply the latest cumulative update. For example, SQL Server 2019 before CU8 had a known issue with single-phase commits on linked servers using OLEDB providers.

How to Prevent This from Happening Again

Once you've fixed it, do these three things to keep it from coming back:

• Document your MSDTC settings on both sides. I keep a text file on each SQL Server with the exact checkboxes checked. When a junior admin resets DTC, I can restore them in two minutes.
• Monitor MSDTC health with this query:

SELECT * FROM sys.dm_tran_active_transactions
WHERE transaction_type = 4; -- Distributed transactions

Run that weekly. If you see transactions stuck in the preparing or prepared state for more than a few minutes, you've got a DTC issue brewing.

• Use local transactions when possible. Not every cross-server query needs distributed transactions. If you're just reading from a linked server, use OPENQUERY with a local connection. Only use distributed transactions when you need atomic commits across servers.

Final thought: I've fixed this exact error at least twenty times. In every case, the problem was either MSDTC security settings or a missing CU. Start with the settings—they're free and fast. If that doesn't work, patch your SQL Server. You'll be back up in under an hour.