In one line: with XACT_ABORT OFF, the default, most errors stop one statement, and the rest of the transaction can still commit. Use XACT_ABORT ON, TRY/CATCH with a rollback, and THROW.
Run it yourself: the scripts that reproduce everything on this page are in sqlserver-demos/error-handling on GitHub.
What you’ll see
- Half a transaction committed: an order saved without its lines, a job that “succeeded” with work missing. In the demo, a duplicate key failed the second of three inserts, and
COMMITsaved the first and third. - Error 3930: “The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.”
- A
CATCHblock that never ran, after a table was dropped or renamed. - An audit trigger that recorded one row for a three-row insert.
Why it happens
Without XACT_ABORT ON, an error like a duplicate key ends the statement that raised it, and execution continues with the next one. The transaction stays open and committable. With XACT_ABORT ON, the same error stops the batch and rolls the whole transaction back.
In a CATCH block, XACT_STATE() says what’s left: 1 is committable, -1 is doomed (only ROLLBACK works), 0 is no transaction. An error raised in a trigger dooms the transaction even with XACT_ABORT OFF.
Some errors can’t be caught at the level where they happen. A table that doesn’t exist is an error when the statement compiles, and goes to the caller. Severity 10 and below is a message, not an error. And RAISERROR continues with the next statement, even under XACT_ABORT ON; THROW stops.
What to do
SET XACT_ABORT ON;at the start of every procedure that opens a transaction.- Use one pattern for every transaction:
“sql SET XACT_ABORT ON; BEGIN TRY BEGIN TRANSACTION; -- the work COMMIT; END TRY BEGIN CATCH IF XACT_STATE() <> 0 ROLLBACK; THROW; END CATCH “
- Raise your own errors with
THROW, notRAISERROR. - Catch missing-object errors in the caller, or run the statement one level down (a procedure or dynamic SQL).
- Write triggers for sets of rows: join to
insertedanddeleted, never read them into variables.
What doesn’t help
TRY/CATCHalone. Without a rollback, aCATCHblock can leave the transaction committable and commit the partial work.XACT_ABORT ONwithRAISERROR.RAISERRORdoesn’t trigger it.
Check your server
-- Procedures in the current database that open a transaction but never
-- mention XACT_ABORT. A starting point for review, not proof of a bug.
SELECT OBJECT_SCHEMA_NAME(m.object_id) + '.' + OBJECT_NAME(m.object_id) AS procedure_name
FROM sys.sql_modules AS m
JOIN sys.procedures AS p ON p.object_id = m.object_id
WHERE m.definition LIKE '%BEGIN TRAN%'
AND m.definition NOT LIKE '%XACT_ABORT%'
ORDER BY procedure_name;
See it happen
The error-handling demo measures:
- half a transaction committed with
XACT_ABORT OFF, and none with it on XACT_STATE()inCATCH, error 3930, and an error from a trigger- errors
TRY/CATCHdoesn’t catch - a trigger written for one row
RAISERRORagainstTHROW
The uncaught errors run in a background Service Broker session, so the demo still runs as one script.
Related: upsert race conditions · deadlock patterns · why is my transaction log full?
Measured on SQL Server 2025 RTM-CU10 (17.0.5035.5) and SQL Server 2022 RTM-CU25 (16.0.4255.1), Linux containers. Your numbers will differ; the behaviour shouldn’t.
Part of SQL Server Demos: short explanations of SQL Server problems, each backed by a runnable demo in MarlonRibunal/sqlserver-demos on GitHub. Written with the help of AI; every number comes from running the demo on SQL Server. How this was made.
