Fix XACT_S_LOCALLY_OK 0X0004D00A Transaction Error

What's This Error About?

You got error 0X0004D00A (XACT_S_LOCALLY_OK). It means a local transaction on your SQL Server didn't get aborted when it should have. The transaction is still open, holding locks, and blocking other work.

This usually happens when a connection gets interrupted—like a power loss, network drop, or a client app crashing. The server doesn't know to roll back the transaction automatically.

I've seen this most often with older apps that use ODBC or OLEDB connections, but it can pop up anywhere. If you're running SQL Server 2019 or 2022, you're more likely to hit this than on newer versions, but it's still possible.

Quick Fix (30 seconds) - Restart the Client App

First, try the simplest thing: restart the application that was running the transaction. If it's a desktop app, close it and reopen it. If it's a web app, recycle the app pool in IIS or restart the web server.

After you do that, check if the error stops. Open SQL Server Management Studio (SSMS) and run this query:

SELECT session_id, open_transaction_count FROM sys.dm_exec_sessions WHERE open_transaction_count > 0;

If you see the count drop to zero, you're done. The app reconnected and cleaned up the transaction.

If the count stays above zero, move to the moderate fix below.

Moderate Fix (5 minutes) - Kill the Hung Session

When restarting the app doesn't work, you need to kill the session holding the transaction. Find the session ID (SPID) first.

Run this in SSMS:

DBCC OPENTRAN;

Look at the output. It will show the oldest active transaction and the SPID. For example: SPID = 52.

Now kill that session:

KILL 52;

Replace 52 with your actual SPID. After running KILL, wait 5-10 seconds, then check again:

DBCC OPENTRAN;

You should see No active open transactions. If the error message goes away from your app logs, you're fixed.

But sometimes KILL doesn't work. The session might be stuck in a rollback. That's where the advanced fix comes in.

Advanced Fix (15+ minutes) - Force Rollback or Restart SQL Server

If KILL hangs or the transaction won't die, you have two options. I'll tell you which one to try first.

Option 1: Check for Deadlocks and Rollback

Run this to see if the transaction is rolling back slowly:

SELECT session_id, status, command, wait_type, wait_time, blocking_session_id FROM sys.dm_exec_requests WHERE session_id = 52;

If the command is KILLED/ROLLBACK, it's rolling back. That's normal—just give it time. Large transactions can take minutes or hours. Wait 15 minutes and check again.

If it's stuck on a wait type like LCK_M_X or PAGELATCH_EX, try killing the blocking session first. Find the blocking session ID from the output above and kill it:

KILL <blocking_session_id>;

Option 2: Restart SQL Server Service

When nothing else works, restart the whole SQL Server service. This is the nuclear option. It will roll back all open transactions on restart, but it takes the server down.

If you can't restart the whole server, just the service:

Open SQL Server Configuration Manager.
Right-click your SQL Server instance and select Stop.
Wait until it's fully stopped (check Services.msc if needed).
Right-click and select Start.

After the service starts, check again:

DBCC OPENTRAN;

It should show no open transactions. The error should disappear.

Preventing This in the Future

Once you fix it, don't let it happen again. Here's what I recommend:

Make sure your client apps handle connection drops properly. They should have a try-catch block that explicitly calls ROLLBACK on any open transaction.
Set a lower query timeout (like 30 seconds) in your connection strings.
Use connection pooling with a short lifetime (like 5 minutes).
For SQL Server 2019+, enable Accelerated Database Recovery (ADR). It handles these scenarios automatically. Run this:

ALTER DATABASE YourDatabaseName SET ACCELERATED_DATABASE_RECOVERY = ON;

ADR makes transaction rollbacks almost instant even for huge transactions. It's a game-changer. I've used it on production servers and it saved me hours of downtime.

When to Call for Help

If you've tried all three fixes and the error still shows up, you might have a deeper issue. Possibly a corrupted transaction log or a bug in SQL Server. In that case, open a support ticket with Microsoft. Have the error code and your steps ready.

But honestly, 99% of the time, one of these fixes will work. Start with the quick one, move down the list. You'll get it sorted.