Home / Software / Tips and Tricks / What is the effect of XACT_ABORT with TRANSACTIONS

What is the effect of XACT_ABORT with TRANSACTIONS

Updated:  03/06/2015 06:03 AM
Author:  Shiju Mathews

Status:    Resolved.


the state of XACT_ABORT specifies whether SQL Server automatically rolls back the current transaction when a Transact-SQL statement raises a run-time error.

Syntax: SET XACT_ABORT { ON | OFF }

When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.

When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing. Depending upon the severity of the error, the entire transaction may be rolled back even when SET XACT_ABORT is OFF.

OFF is the default setting.

The following example will help to understand the concept better.