Fix STATUS_TRANSACTION_INVALID_ID 0XC0000214 on SQL Server

You're running a query that spans two databases—maybe on a linked server or using a distributed transaction—and suddenly SQL Server hits you with STATUS_TRANSACTION_INVALID_ID (0XC0000214). The error text says the transport doesn't recognize the specified transaction request ID. This typically happens mid-query, not at the start. I've seen it when someone kicks off a cross-server UPDATE and the transaction rolls back, but the client keeps trying to commit. Another common trigger: a dead session that hasn't been cleaned up, or a DTC (Distributed Transaction Coordinator) service that's been restarted while connections were still open.

Let's break down what's going on. When you use a distributed transaction—like updating a table on Server A and another on Server B—SQL Server hands the transaction to Microsoft Distributed Transaction Coordinator (MSDTC). MSDTC keeps a list of active transaction IDs. If that list gets out of sync—say MSDTC restarts, or a connection is orphaned—the transport layer sees a transaction ID it doesn't have on file. That's your 0XC0000214. It's not a permissions problem. It's not a syntax error. It's a stale reference. The fix is about clearing that state and making sure MSDTC is stable.

Here's the fix I've used on more than one client's production box. It's not glamorous, but it works.

1. Check if MSDTC is running. Open Services (services.msc) and look for Distributed Transaction Coordinator. If it's stopped, start it. If it's running, restart it. I had a client last month whose DTC had been stopped for weeks—they only found out when a cross-server query finally needed it. Restarting cleared the bogus transaction IDs right away.
2. Kill any orphaned sessions. Run this on each SQL Server instance involved:

SELECT session_id, login_name, status
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
AND status = 'sleeping'
AND last_request_end_time < DATEADD(hour, -1, GETDATE());

If you see sessions that haven't done anything in over an hour, kill them with KILL <session_id>. Orphaned sessions often hold references to transactions that MSDTC no longer tracks. Clearing them is like turning off a walkie-talkie that's still on a dead channel.

3. Disable and re-enable distributed transactions on the linked server. If this error appears only when you use a specific linked server, open that server's properties in SSMS. Go to the Server Options page and set Enable Promotion of Distributed Transactions to False. That makes SQL Server run the transaction locally if possible, avoiding MSDTC altogether. For rare cross-server queries, this is often the cleanest workaround.
4. Test with a fresh connection. Close SSMS or your application, then reconnect. I've seen this error hang around in a connection pool. A new connection can't reference a stale transaction ID because it doesn't have one.
5. If you're using a VPN or firewall, check that MSDTC ports are open. MSDTC uses dynamic TCP ports, usually in the 49152-65535 range, plus RPC port 135. If your network blocks those, MSDTC can't maintain transaction state. I once spent an afternoon on a server where the client's firewall was reset and the new rules didn't include MSDTC. The error came and went randomly, but it was always after the firewall change.

Still seeing 0XC0000214? Then dig deeper:

• Check the Windows Event Log under Application for MSDTC entries. Look for event ID 4181 or similar—that'll tell you if MSDTC is actually the problem.
• Run dtcping from the command line to test connectivity between the two servers. If it fails, you've got a network issue, not a SQL issue.
• Look at your application code. If it's using explicit BEGIN DISTRIBUTED TRANSACTION, make sure it's not manually committing after a rollback. That's a classic way to produce a phantom transaction ID.

In my experience, 9 times out of 10 this error is a red herring—the real issue is a dead MSDTC service or a stale connection. Restart the service, kill old sessions, and you're back in business. Don't overthink it.