What is SET XACT_ABORT in SQL Server?
In this post “SET XACT_ABORT in SQL Server”, we will discuss about the XACT_ABORT command. SET XACT_ABORT controls the atomicity of any user defined transaction. By default SET XACT_ABORT is OFF. It specifies whether SQL Server automatically rollback and abort a transaction if any error occurred in any statement of the transaction. If SET XACT_ABORT is ON, if any run time error occur in any t-sql statement of the transaction, entire transaction is terminated and rolled back. If SET XACT_ABORT is OFF, only that statement which raised the error is rolled back and transaction continues the processing of other statements.
Note that if the severity of the error is high, entire transaction can be rolled back even if SET XACT_ABORT is OFF.
Scope of XACT_ABORT
Scope of XACT_ABORT is at connection level and remains until either reconfigured or connection is closed. Note that … More