SQL Server Optimization – Finding Stored Procedures with XACT ABORT ON or OFF

optimizationquerysql serversql server 2014stored-procedures

I am trying to troubleshoot blocking problems related to active open transaction with sleeping status and probably have found issues related to XACT ABORT option missing or as OFF read from many blog post online–

  1. Is there a way to find what stored procs are missing those
    statements for a database?

  2. As SQL by default has this off, is there a harm enabling at SQL
    instance to ON or is it recommended to have only some SP to turn
    this on as. Why i am asking because as mentioned here it seems
    this should be added to all SP as best practice, is that true? if
    yes why not enable this at SQL instance level?

Best Answer

This query will show you all T-SQL modules (i.e. stored procedures, functions, etc) on a SQL Server instance that have XACT_ABORT in their code:

DECLARE @cmd nvarchar(max) ;
SET @cmd = N'';
SELECT @cmd = @cmd + CASE WHEN (@cmd = N'') THEN N'' ELSE N'UNION ALL
' END + N'SELECT ServerName = @@SERVERNAME COLLATE SQL_Latin1_General_CP1_CI_AS
    , db = ''' + d.name + N''' COLLATE SQL_Latin1_General_CP1_CI_AS
    , ObjectName = s.name + N''.'' + o.name COLLATE SQL_Latin1_General_CP1_CI_AS
    , [definition] = sm.definition COLLATE SQL_Latin1_General_CP1_CI_AS
FROM ' + QUOTENAME(d.name) + N'.sys.sql_modules sm
    INNER JOIN ' + QUOTENAME(d.name) + N'.sys.objects o ON sm.object_id = o.object_id
    INNER JOIN ' + QUOTENAME(d.name) + N'.sys.schemas s ON o.schema_id = s.schema_id
WHERE sm.definition LIKE N''%XACT_ABORT%'' COLLATE SQL_Latin1_General_CP1_CI_AS
'
FROM sys.databases d
WHERE d.database_id > 4
    AND d.state_desc = N'ONLINE'
ORDER BY d.name;
EXEC sys.sp_executesql @cmd;

One could rewrite that to show code without XACT_ABORT present, by simply adding NOT to the WHERE clause, as in:

DECLARE @cmd nvarchar(max) ;
SET @cmd = N'';
SELECT @cmd = @cmd + CASE WHEN (@cmd = N'') THEN N'' ELSE N'UNION ALL
' END + N'SELECT ServerName = @@SERVERNAME COLLATE SQL_Latin1_General_CP1_CI_AS
    , db = ''' + d.name + N''' COLLATE SQL_Latin1_General_CP1_CI_AS
    , ObjectName = s.name + N''.'' + o.name COLLATE SQL_Latin1_General_CP1_CI_AS
    , [definition] = sm.definition COLLATE SQL_Latin1_General_CP1_CI_AS
FROM ' + QUOTENAME(d.name) + N'.sys.sql_modules sm
    INNER JOIN ' + QUOTENAME(d.name) + N'.sys.objects o ON sm.object_id = o.object_id
    INNER JOIN ' + QUOTENAME(d.name) + N'.sys.schemas s ON o.schema_id = s.schema_id
WHERE sm.definition NOT LIKE N''%XACT_ABORT%'' COLLATE SQL_Latin1_General_CP1_CI_AS
'
FROM sys.databases d
WHERE d.database_id > 4
    AND d.state_desc = N'ONLINE'
ORDER BY d.name;
EXEC sys.sp_executesql @cmd;

However, you'll notice there are several possibilities for false positives:

  1. If you have a column named XACT_ABORT in the code.
  2. You have a comment such as /* must not use XACT_ABORT in this code */

etc.

To be honest, your question sounds like an XY problem. Are you sure you want to determine which code uses XACT_ABORT? A stored procedure won't cause an open transaction to "hang around". The most likely cause is a code window open in SSMS that has BEGIN TRANSACTION where the corresponding ROLLBACK or COMMIT haven't been executed. Alternately, there could be some legitimate long-running code keeping transactions open. One common issue that results in long running transaction is code using the RBAR method.

Regarding your second question, I'd be extremely reluctant to ever say a particular set statement should "always" be used, although I do tend to use SET XACT_ABORT = ON a lot. Erland Sommarskog is very highly regarded in the SQL Server community, but I don't agree with this:

stored procedures should always include this statement in the beginning:

SET XACT_ABORT, NOCOUNT ON
Related Question