Saturday, April 4, 2015

Very useful SET XACT_ABORT TIP#94

SET XACT_ABORT is one of the hidden gem we can say. It is helpful in many ways.
Let me explain by an example suppose you are working on a stored procedure which is doing a complex data manipulation. You though this query will run with in 1 minute when you run it from .NET or other code , but some how it took quite long time and with in one minute .NET or the code from which you run the query raised a command timeout.
Or we can say you have applied transaction in stored procedure and calling from .NET code and you have mention the command timeout in .NET .The ADO.NET program raised a timeout but still your query is running in background.
Now in such situation sometimes it happened the transaction which is running in SQL SERVER will be open transaction. Which might be  cause of locking problem.
In such situation  XACT_ABORT option is helpful.
By default XACT_ABORT option is off you can on it by following statement
Now when this statement is on and if you face same situation as mentioned above it will rollback entire batch.
Let me explain with below example
Suppose I wrote following statement “Notice here XACT ABORT is OFF”
Now , when I run the above statements I will get following result in result window
And in the message window we will get following result
What it means instead of the error at least 3 records are inserted but sometimes you require either all or no record commit in the table then let me enable the option XACT_ABORT ON in the query
Now let me re run the statements again. When I run the above statements I got following result in message tab there is no result tab.what is means ??
It means what ever inserted is roll backed when an error occurred in a statement. Although The above example is not the best example of XACT_Abort but it will help to understand how to use it.

I appreciate your inputs on this valuable topic.


  1. Hi Rajat,
    Please ignore previous post(same as this). It was by mistaken done from different account.

    I tired above examples in my query editor.
    As per your comments when we use "set xact_abort on" then it will not store values in #temp table i.e it rolled back all transaction done after "set xact_abort on" command. Please correct me if I understood wrong.

    My confusion here is when I fired query using "set xact_abort on",
    then it should not store values in #temp table. right? But I am finding the same data in #temp table that means no transactions are rolled back.


    1. Thanks Anuj.

      Please see my comment below :)

  2. Dear Anuj,
    Yes you are right when SET XACT_ABORT ON it revert explicit transaction batch.
    Sorry, It might be possible I was not able to convey a proper message what exactly I mean to say here.

    Please review one of the best article which I found so far