Fix STATUS_TRANSACTION_NOT_ROOT 0XC0190054 in SQL Server

SQL Server error 0XC0190054 means a transaction wasn't started at the root. Restart the service first, then check for linked server or XACT_ABORT issues.

Quick answer

Restart SQL Server service, then check if XACT_ABORT is off in your session — if it is, turn it ON. If the error persists after a restart, look for linked server calls inside a local transaction.

What's going on here?

This error is a SQL Server transaction nesting nightmare. The code 0XC0190054 maps to STATUS_TRANSACTION_NOT_ROOT — it means SQL Server's transaction manager thinks a transaction is trying to commit or roll back from a nested level that isn't the outermost one. In plain English: something in your code called COMMIT or ROLLBACK when the transaction wasn't started at that level.

I've seen this a hundred times in production. Most often it's a stored procedure that has a BEGIN TRANSACTION inside a TRY...CATCH block and then tries to commit after a linked server call fails. Or it's an application using distributed transactions (MSDTC) where the root transaction gets confused. The culprit is almost always one of those two, though sometimes it's just a corrupted session state after a failover.

Fix steps (in order)

Restart the SQL Server service. Don't skip this. If the error is from a stuck session or a corrupted transaction context, a restart clears it. On a standalone instance:
```
net stop MSSQLSERVER
net start MSSQLSERVER
```
For a named instance, replace MSSQLSERVER with MSSQL$YourInstanceName. If you can't restart right now, at least run DBCC FREESESSIONCACHE and KILL the specific SPID that's throwing the error.
Check XACT_ABORT setting. The most common cause. Run this on the session that gets the error:
```
SELECT XACT_STATE(), @@OPTIONS & 16384
```
If the second column returns 0, XACT_ABORT is OFF. Many ORMs (especially older ADO.NET code) turn it off by default. Set it ON at the start of your stored procedure:
```
SET XACT_ABORT ON
```
This forces SQL Server to roll back the entire transaction on any runtime error, which prevents the nested commit/rollback mess.
Review linked server calls inside transactions. If you have a BEGIN TRANSACTION in T-SQL and then call a linked server (like SELECT * FROM REMOTESERVER.DB.dbo.Table), the remote call might start its own transaction. If that call fails, the local transaction gets confused. Test with SET REMOTE_PROC_TRANSACTIONS OFF (deprecated but still works) or better, restructure the code to avoid remote calls inside a local transaction. Move the linked server query before the BEGIN TRAN or after the commit.
Check for nested transactions in your code. Look for multiple BEGIN TRANSACTION statements without proper matching. Remember, SQL Server doesn't support true nesting — it just counts @@TRANCOUNT. If you have a procedure that calls another procedure that also starts a transaction, the inner one should never commit or roll back on its own. Use SAVE TRANSACTION instead of BEGIN TRANSACTION in the inner procedure, or check @@TRANCOUNT before starting a new one.

If the main fixes don't work

Sometimes the error is from an application that uses MSDTC (distributed transactions). In that case, make sure MSDTC is configured correctly on all servers involved. Check Windows Event Log for MSDTC errors around the same time. Also, if you're on SQL Server 2008 or older (and I hope you're not), consider upgrading — that version had known bugs with transaction nesting in some edge cases.

Another trick: run DBCC OPENTRAN to see if there's a long-running open transaction. If you find one, you can kill it with KILL spid — but be careful, that might roll back a lot of work.

Prevention tips

Always set SET XACT_ABORT ON in stored procedures that use transactions — it's a habit that saves you from many headaches.
Never call a linked server inside a local transaction unless you absolutely have to. If you must, test with a small dataset first.
Use TRY...CATCH properly — in the CATCH block, check XACT_STATE() before deciding to rollback. If it's -1, you must rollback; if it's 0, there's nothing to rollback; if it's 1, you can commit.
Monitor your error logs for 0XC0190054. If it shows up more than once a week, you have a code issue that needs fixing, not just restarting.

Bottom line: restart the service first to get back up, then make XACT_ABORT ON a standard in your codebase. The linked server angle is the next suspect — I'd bet money it's one of those two. Fix it before it bites you again.