2

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?

4

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
|improve this answer|||||
  • Awesome, thanks, I will certainly check on that XY in more. But can you recall a situation where XACT ABORT should be OFF or procedures better to have that option than to be ON? – BeginnerDBA Jan 9 at 16:10
  • Also why i am going towards XACT ABORT is because we have app with lock time out X seconds and if it does not complete, gets timed out and application seems to be handling that cleanup which in that process has active transactions which causes blocking. I hope that makes sense – BeginnerDBA Jan 9 at 16:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.