Fix 0XC019004D: Too Many Unaborted Transactions

Yeah, that error is a pain. You're mid-query and suddenly the whole database grinds to a halt with 0XC019004D. I've seen it on SQL Server 2016 and 2019, typically after a bulk import or when an app forgets to commit a loop. Let's fix it.

The Fix (Do This First)

We're going to force-kill the stuck transactions, bump up the worker count, and restart the service. You'll need sysadmin rights on SQL Server and access to the host OS. If you're on Azure SQL, skip this—it's on-prem only.

Open SQL Server Management Studio (SSMS) and run this query to find blocking sessions:

SELECT session_id, status, command, wait_type, wait_time, blocking_session_id
FROM sys.dm_exec_requests
WHERE blocking_session_id > 0;

You should see one or more rows with a command like UPDATE or INSERT and a wait_type of LCK_M_X or similar. Note the session_id values.

Kill those sessions with:

KILL 62;  -- replace 62 with the actual session_id

After each KILL, you should see "Command(s) completed successfully." If you get an error like "Cannot kill..." then the session is stuck in an unkillable state—move to step 3.

If KILL doesn't work, run this to see which transactions are still active:

SELECT s.session_id, t.transaction_id, t.name, t.state, t.is_user_transaction
FROM sys.dm_tran_session_transactions s
JOIN sys.dm_tran_active_transactions t ON s.transaction_id = t.transaction_id
WHERE t.state = 1;  -- 1 means active

Look for transactions with a state of 1 and no associated session that's actively running. Those are the orphans. Note the transaction_id.

Run this to mark those transactions for rollback (this is the nuclear option):

-- Rollback all open transactions in the database
ALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE YourDatabase SET MULTI_USER;

After the first ALTER, you might see a message like "Rollback of transactions started." It could take a few minutes if the transactions were big. When it's done, the second ALTER should complete fast.

Now bump up the max worker threads. Open Services (Win+R, type services.msc), find SQL Server (MSSQLSERVER), right-click, and stop it. Then start it again. But before that, run this query to set a higher number:

EXEC sp_configure 'max worker threads', 1024;
RECONFIGURE;

If you get a message about needing to restart, that's expected. Reconfigure takes effect after the service restarts, so do the restart now. After the service is back, run your original query—it should work.

Why This Works

The error 0XC019004D comes from the Windows Kernel Transaction Manager (KTM). When SQL Server runs out of worker threads (the default is 512 or 1024 depending on CPU count), it can't process new transactions. Old ones that should have been aborted are stuck waiting. By killing them and rolling back, you free up the worker threads. Increasing the max also gives you headroom so this doesn't happen again on the next big batch.

The real fix is to stop the leak at the source—your application code. But you already know that.

Less Common Variations

Sometimes 0XC019004D appears in the Windows Event Log, not just SQL errors. If you're seeing it in a clustered environment or with MSDTC (distributed transactions), the fix is slightly different.

Variation 1: MSDTC Stuck

If you're using distributed transactions (e.g., linked servers), the MSDTC service might have deadlocked transactions. Restart MSDTC:

net stop msdtc
net start msdtc

You'll need to run those in an elevated command prompt. After restart, try your query again. No SQL restart needed.

Variation 2: File System Transactions (NTFS)

If this error happens outside SQL, like in a custom app using Transactional NTFS (TxF), the fix is to update the application. TxF is deprecated since Windows 10, so you should consider moving to a different transaction method. Short-term, a reboot clears the stuck transactions, but it'll come back.

Variation 3: High CPU Load

On a hyperthreaded server with many cores, the default max worker threads might be too low. Check your current setting:

EXEC sp_configure 'max worker threads';

If it's below 1024 and you have 16+ cores, set it to 2048. You might see a performance boost, not just error prevention.

Prevention: Keep This From Happening Again

Set a command timeout in your application. Most ORMs (like Entity Framework) have a CommandTimeout property—set it to 30 seconds. If a query hangs, it'll fail fast instead of piling up.
Use READ COMMITTED SNAPSHOT to reduce blocking. This adds a version store, but it's worth it for OLTP workloads.
Monitor for long-running transactions with this alert:

SELECT * FROM sys.dm_tran_active_transactions 
WHERE DATEDIFF(SECOND, transaction_begin_time, GETDATE()) > 300;

Set a SQL Agent job to run this every 5 minutes and email you if it returns rows.

Review your code for missing COMMIT statements. A classic bug is an exception handler that doesn't roll back. Wrap your transactions in a try-catch and roll back on error.

That's it. The error is annoying, but it's solvable in less than 20 minutes if you follow the steps. If you're still stuck after trying this, check if there's a pending Windows update—Microsoft patched a related KTM bug in KB4474419, and sometimes an old OS causes this.