Fix STATUS_TRANSACTION_NOT_JOINED (0XC0190007) in SQL Server

SQL Server error 0XC0190007 means a resource manager tried to prepare a transaction it never joined. Usually a distributed transaction coordination issue, often from orphaned sessions or misconfigured MSDTC.

Quick answer: Kill the orphaned session that's holding the transaction, restart MSDTC, and make sure your linked server queries aren't splitting a single transaction across multiple connections.

What's actually happening here is that SQL Server's kernel transaction manager (KTM) expects each resource manager — like a database, a file system, or a linked server — to explicitly join a transaction before it can prepare for commit. The error 0XC0190007 pops up when some component tries to prepare a transaction it never officially joined. It's almost always a distributed transaction problem, meaning you've got something like a linked server query, a cross-database transaction, or a .NET TransactionScope that's trying to coordinate multiple resources.

I've seen this exact error on SQL Server 2019 with a linked server to Oracle. The query would run fine for weeks, then suddenly throw 0XC0190007 and the whole batch would roll back. The culprit was a connection pool that kept a session alive after the underlying transaction had already been aborted. The session still thought it was part of the transaction, but the KTM had already forgotten about it.

Another common trigger is when MSDTC (Microsoft Distributed Transaction Coordinator) gets restarted or becomes unavailable mid-transaction. The resource managers lose their state, and when they try to prepare, they realize they're not joined anymore.

Step-by-Step Fix

Find the orphaned session. Run this query to see which sessions are holding transaction state:

SELECT session_id, transaction_id, is_user_transaction, is_local, open_transaction_count
FROM sys.dm_tran_session_transactions
WHERE open_transaction_count > 0;

You're looking for sessions that show an open transaction but haven't done anything in a while. Cross-reference with sys.dm_exec_requests and sys.dm_exec_sessions to see the last batch and login timestamp.

Kill the offending session. Once you've identified the session ID, run:

KILL 123;  -- replace with actual session_id

This forces the session to roll back. If it's stuck in a rollback that never completes, you might need KILL 123 WITH STATUSONLY first to see progress. In extreme cases, you may need to restart SQL Server, but try all other options first.

Restart MSDTC. On the SQL Server machine (and on the remote server if it's a linked server scenario), open Services and restart the Distributed Transaction Coordinator. Make sure it's set to Automatic startup.

net stop msdtc && net start msdtc

Check MSDTC security settings. Open dcomcnfg, navigate to Component Services → Computers → My Computer → Distributed Transaction Coordinator → Local DTC. Right-click and select Properties. Go to the Security tab and make sure "Network DTC Access" is enabled if you're using remote transactions. Also check that "Allow Inbound" and "Allow Outbound" are both checked for Transaction Manager Communication. If you're not using network transactions, you can actually disable Network DTC Access to reduce the attack surface — but if you need it, make sure it's on.

Enable XA transactions for linked servers. If you're using a linked server with XA-compliant databases (like Oracle), you might need to enable XA transactions in MSDTC. In the same Security tab, check "Enable XA Transactions." This is often the missing piece when the error appears only with certain linked servers.

If the main fix doesn't work

Sometimes the session you killed was just a symptom. The real issue is a misconfigured or crashed MSDTC. In that case:

Clear the MSDTC log — this can help if the log got corrupted. Stop MSDTC, delete the log files in C:\Windows\System32\MSDTC (you'll see files like MSDTC.log), then restart. Yes, this is a bit nuclear, and you'll lose any pending transaction state, but it's often the only way to get a stuck MSDTC back to health.
Check for firewalls between servers. MSDTC uses port 135 and a dynamic range of ports. If your firewall blocks these, transactions will fail with this error when the resource manager can't connect back to the coordinator. Use netstat -an to see if connections to the remote DTC are being established.
Update your connection string. If you're using .NET SQLClient, make sure you're not accidentally enabling distributed transactions when you don't mean to. Setting Enlist=false in the connection string prevents a single connection from automatically enlisting in a TransactionScope. This is a common fix when the error only happens in a specific application.

Prevention

The root cause is almost always a transaction that's left half-open. So the best prevention is to make sure your application code always commits or rolls back in a finally block. Specifically:

Keep transaction lifetimes short. A transaction that's open for more than a few seconds is a candidate for this error, especially if it's spanning multiple servers.
Set a timeout on your TransactionScope — 30 seconds max. If it times out, it'll throw a different error, but at least it won't hang around causing this one.
If you're using linked servers, consider re-architecting to avoid distributed transactions entirely. Often you can pull the remote data into a temp table first, then work with it locally. That eliminates the need for MSDTC and this entire class of errors.

One last thought — don't ignore this error when it appears once. It usually means something is misconfigured at a level that'll bite you again. Fix it properly the first time.