none
What is the XACT_ABORT setting on the connection?

    Question

  • Hi, I've been looking for a place to find the current XACT_ABORT setting of an existing connection.  Is there a place where this in available in a dmv or SQL Profiler?  I looked at the output of Existing Connections in the profiler.  It does not seem to be one of the items listed. 

    Thanks.


    Randy in Marin

    Monday, December 17, 2012 5:34 PM

Answers

  • You could use XEvents. Here is sample session definition. (I have tested it on SQL 2012 SP1)

    Please replace the file path in the following script to a valid file path on your machine where SQL Server service account has read, write privileges to write to XEvent file.

     

    1) Create Event Session:

    CREATE EVENT SESSION [set_options] ON SERVER 
    ADD EVENT sqlserver.existing_connection(SET collect_database_name=(1),collect_options_text=(1)) 
    ADD TARGET package0.event_file(SET filename=N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\set_options.xel')
    WITH (MAX_MEMORY=4096 KB,
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY=30 SECONDS,
    MAX_EVENT_SIZE=0 KB,
    MEMORY_PARTITION_MODE=NONE,
    TRACK_CAUSALITY=OFF,
    STARTUP_STATE=OFF)
    GO

    2) Start Event Session:

    ALTER EVENT SESSION [set_options] ON SERVER STATE=START

    3) Then you can open the *.xel file in SSMS and view captured events

    Keep us posted if this approach works for you


    Thanks, Sethu Srinivasan [MSFT] SQL Server http://blogs.msdn.com/sqlagent -------------------------------------------------------------------------------- This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    Saturday, January 05, 2013 9:41 PM
    Moderator

All replies

  • Hello,

    Please use the following T-SQL code to know if XACT_ABORT is set On on the current connection. XACT_ABORT should be listed as result if this option is set On.


    DECLARE @options INT

    SELECT @options = @@OPTIONS

    PRINT @options

    IF ( (1 & @options) = 1 ) PRINT 'DISABLE_DEF_CNST_CHK'

    IF ( (2 & @options) = 2 ) PRINT 'IMPLICIT_TRANSACTIONS'

    IF ( (4 & @options) = 4 ) PRINT 'CURSOR_CLOSE_ON_COMMIT'

    IF ( (8 & @options) = 8 ) PRINT 'ANSI_WARNINGS'

    IF ( (16 & @options) = 16 ) PRINT 'ANSI_PADDING'

    IF ( (32 & @options) = 32 ) PRINT 'ANSI_NULLS'

    IF ( (64 & @options) = 64 ) PRINT 'ARITHABORT'

    IF ( (128 & @options) = 128 ) PRINT 'ARITHIGNORE'

    IF ( (256 & @options) = 256 ) PRINT 'QUOTED_IDENTIFIER'

    IF ( (512 & @options) = 512 ) PRINT 'NOCOUNT'

    IF ( (1024 & @options) = 1024 ) PRINT 'ANSI_NULL_DFLT_ON'

    IF ( (2048 & @options) = 2048 ) PRINT 'ANSI_NULL_DFLT_OFF'

    IF ( (4096 & @options) = 4096 ) PRINT 'CONCAT_NULL_YIELDS_NULL'

    IF ( (8192 & @options) = 8192 ) PRINT 'NUMERIC_ROUNDABORT'

    IF ( (16384 & @options) = 16384 ) PRINT 'XACT_ABORT'

    Hope this helps.


    Regards,
    Alberto Morillo
    SQLCoffee.com

    Tuesday, December 18, 2012 5:50 AM
    Moderator
  • Thanks for the information, but I'm trying to get the setting for a non-local connection.  We are porting code from an IBM mainframe to Microfocus .Net COBOL.  The product we are using maintains many connections.  Some of them are left with an open transaction from time to time.  I'm trying to see if the XACT_ABORT is set on those connections.  I have no access to the connection nor can I alter client code. 

    Sometimes the connection with an open transaction is holding locks that results in a blocking.  The blocked process report has two client option values.  One of them has the XACT_ABORT setting.  I want to see the value before there are issues.  I was hoping to profile the server and look at the history of the connection.  If XACT_ABORT is not always on, then perhaps....  However, I don't know how to find this value in a trace. 


    Randy in Marin

    Tuesday, December 18, 2012 9:59 PM
  • I want the value for an "existing" session, not the current one.  I want to be able to track the XACT_ABORT setting on all sessions over time.  It is not at all obvious to me how to do this.  Perhaps this is not possible?  If it is not possible, I will suggest it be a feature in a future version. 

    Randy in Marin

    Monday, December 31, 2012 4:51 PM
  • Hi there,

    i wonder if you can find the current session value. XACT_ABORT can be override at few places.

    I think the best solutions for this is inform your development team to handle the transactions correctly. They can avoid by code reviewing.

    i have noticed this option was frequently used by database developers and never used by middle ware developers like .net, java etc.

    thanks

    kumar

    Thursday, January 03, 2013 12:55 AM
  • You could use XEvents. Here is sample session definition. (I have tested it on SQL 2012 SP1)

    Please replace the file path in the following script to a valid file path on your machine where SQL Server service account has read, write privileges to write to XEvent file.

     

    1) Create Event Session:

    CREATE EVENT SESSION [set_options] ON SERVER 
    ADD EVENT sqlserver.existing_connection(SET collect_database_name=(1),collect_options_text=(1)) 
    ADD TARGET package0.event_file(SET filename=N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\set_options.xel')
    WITH (MAX_MEMORY=4096 KB,
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY=30 SECONDS,
    MAX_EVENT_SIZE=0 KB,
    MEMORY_PARTITION_MODE=NONE,
    TRACK_CAUSALITY=OFF,
    STARTUP_STATE=OFF)
    GO

    2) Start Event Session:

    ALTER EVENT SESSION [set_options] ON SERVER STATE=START

    3) Then you can open the *.xel file in SSMS and view captured events

    Keep us posted if this approach works for you


    Thanks, Sethu Srinivasan [MSFT] SQL Server http://blogs.msdn.com/sqlagent -------------------------------------------------------------------------------- This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    Saturday, January 05, 2013 9:41 PM
    Moderator
  • Hi Kumar.  It's our code in the end, but we have not generated it.  It's the product of a conversion done by a third party.  In addition, the transactions are likely handled by yet another product that the vendor is using.  Having a trace that includes the XACT_ABORT setting would help.  I thought a good start would be to see when the issue starts.  If I could see where the problem starts, then I might be able to identify who can fix it. 


    Randy in Marin

    Monday, January 07, 2013 8:30 PM
  • Thanks Sethu.  Unfortunately, we are using SQL 2008 R2.  I see the "existing_connection" listed in this query when using 2012, but not when using 2008 R2. 

    select * from sys.dm_xe_objects where object_type = 'event' order by name

    I don't see a "collect_options_text" when this is run on SQL 2008 R2. 

    select * from sys.dm_xe_object_columns order by name 

    I will keep this in mind when using 2012.  I suppose this means the 2012 profiler can capture this as well? 

    Thanks.


    Randy in Marin

    Monday, January 07, 2013 8:59 PM
  • Having a trace that includes the XACT_ABORT setting would help.  I thought a good start would be to see when the issue starts.  If I could see where the problem starts, then I might be able to identify who can fix it. 

    Why don't you just ask the responsible parties to look in their code for XACT_ABORT?  It should be easy to find.

    I would just call a meeting and discuss the issues with them. Let them fix the issues, the culprit may not even be XACT_ABORT.


    Kalman Toth SQL 2008 GRAND SLAM
    New Book: SQL Server 2012 Programming


    Monday, January 07, 2013 9:34 PM
    Answerer
  • The vendor is trying to identify the issue.  I was hoping to assist.  I can still run the trace, but I think it would be important to know the XACT_ABORT setting on the connection from the time it is opened to the point of the connection being reused.  I suppose I can trace every statement and look for SET XACT_ABORT.  The culprit is likely not XACT_ABORT, but it would be nice to verify without tracing the path though code. 


    Randy in Marin

    Monday, January 07, 2013 11:19 PM
  • I went ahead and marked an answer for the 2012 version.  I will assume it can't be done for 2008R2. 


    Randy in Marin

    Thursday, October 02, 2014 12:25 AM