T-SQL error handling: XACT_ABORT and TRY/CATCH

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 COMMIT saved 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 CATCH block 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, not RAISERROR.
  • 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 inserted and deleted, never read them into variables.

What doesn’t help

  • TRY/CATCH alone. Without a rollback, a CATCH block can leave the transaction committable and commit the partial work.
  • XACT_ABORT ON with RAISERROR. RAISERROR doesn’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() in CATCH, error 3930, and an error from a trigger
  • errors TRY/CATCH doesn’t catch
  • a trigger written for one row
  • RAISERROR against THROW

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.

Verified by MonsterInsights