Today’s post will explain XACT_ABORT in SQL Server. An efficient way of error handling before SQL Server 2005.Post SQL Server 2005, there is a new feature included in SQL Server, TRY… CATCH. But, I would still think there is a good amount of use cases and a great value addition where XACT_ABORT is important in SQL Server. Let us cover few things in the below sections.

Whats the significance of XACT_ABORT in SQL Server?

On a run time error scenario, XACT_ABORT On settings will terminate and rollback the entire transaction. XACT_ABORT does not have any significance on compile or parse time exceptions.

How do we set up XACT_ABORT in SQL Server?

A simple and common method is to set using T-SQL as below:

SET XACT_ABORT {ON/OFF}

Another option is at Database Engine level as below:

EXEC sp_configure 'user options', 16384
RECONFIGURE WITH OVERRIDE

You can refer more about this here.

We can always understand the current setting of XACT_ABORT as below:

DECLARE @XACT_ABORT VARCHAR(3)  = 'OFF'
IF ( (16384 & @@OPTIONS) = 16384 ) SET @XACT_ABORT = 'ON';  
SELECT @XACT_ABORT AS XACT_ABORT; 

Whats the scope if XACT_ABORT?

— SET XACT_ABORT ON/OFF is applied ONLY for the session
— By default XACT_ABORT is OFF

— XACT_ABORT ON, Try…Catch & Transaction

As mentioned earlier, by default XACT_ABORT is OFF by default and if there are any issue in one of transaction of a batch, the transaction alone gets failed as below:

There is no difference even with XACT_ABORT ON in similar case as below:

However, There is a difference in XACT_ABORT when its applied with a transaction. If there are any issue in a batch, the entire batch gets terminated as below:(please note, there is no explicit rollback applied in the example, still no transactions are committed.)

— XACT_ABORT, Transaction and Object Resolution

This is interesting to know about the fact that if there are any failures, the transaction becomes open without XACT_ABORT. If we specify the XACT_ABORT ON, then the transaction is terminated automatically.

Finally, How to preserve XACT_ABORT state in SQL Server?

Sometimes, preserving the XACT_ABORT is a requirement for many of us. As we discussed, we can use @@options and a bit operation with the corresponding user options value to preserve the value. Here is a small example, how can this be achieved as below:

Create procedure Preserve_XACTABORT_Settings
as
Begin

	Declare @Options Bigint
	SET @Options = @@options

	--To know the setting of XACT_ABORT for testing purpose
	DECLARE @XACT_ABORT VARCHAR(3)  = 'OFF'
	IF ( (16384 & @@OPTIONS) = 16384 ) SET @XACT_ABORT = 'ON' Else Set @XACT_ABORT = 'OFF'
	SELECT @XACT_ABORT AS XACT_ABORT; 

	SET XACT_ABORT ON

	/*

	You can put your actual procedure implementation

	*/

	--To know the setting of XACT_ABORT for testing purpose
	IF ( (16384 & @@OPTIONS) = 16384 ) SET @XACT_ABORT = 'ON' Else Set @XACT_ABORT = 'OFF' 
	SELECT @XACT_ABORT AS XACT_ABORT; 	


	--Preserve the XACT_ABORT settings
	IF ( (@OPTIONS & 16384 ) = 0 ) 
		SET XACT_ABORT OFF

	--To know the setting of XACT_ABORT for testing purpose
	IF ( (16384 & @@OPTIONS) = 16384 ) SET @XACT_ABORT = 'ON' Else Set @XACT_ABORT = 'OFF'
	SELECT @XACT_ABORT AS XACT_ABORT; 	

End

Hope, you enjoyed this post, share your thoughts and feedback as always!