使用 XACT ABORT ON 或 OFF 查找儲存過程

我正在嘗試解決與處於睡眠狀態的活動打開事務相關的阻塞問題，並且可能發現與 XACT ABORT 選項失去或從許多線上部落格文章中讀取的 OFF 相關的問題-

1. 有沒有辦法找到哪些儲存過程缺少數據庫的這些語句？
2. 由於預設情況下 SQL 已關閉此功能，因此在 SQL 實例上啟用此功能是否有害，或者建議僅讓一些 SP 將其打開。為什麼我要問，因為正如這裡提到的，這似乎應該作為最佳實踐添加到所有 SP，這是真的嗎？如果是，為什麼不在 SQL 實例級別啟用它？

此查詢將向您顯示 SQL Server 實例上的所有 T-SQL 模組（即儲存過程、函式等），XACT_ABORT它們的程式碼中包含：

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;

可以通過簡單地添加到子句來重寫它以顯示不存在的程式碼，如下所示： XACT_ABORT``NOT``WHERE

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;

但是，您會注意到誤報有多種可能性：

1. 如果您有XACT_ABORT在程式碼中命名的列。
2. 您有評論，例如/* must not use XACT_ABORT in this code */

等等

老實說，您的問題聽起來像是XY 問題。您確定要確定使用哪些程式碼XACT_ABORT嗎？儲存過程不會導致打開的事務“徘徊”。最可能的原因是在 SSMS 中打開的程式碼視窗具有BEGIN TRANSACTION相應的ROLLBACK或COMMIT尚未執行的位置。或者，可能有一些合法的長時間執行的程式碼保持交易開放。導致事務長時間執行的一個常見問題是使用RBAR 方法的程式碼。

關於你的第二個問題，我非常不願意說應該“總是”使用特定的集合語句，儘管我確實傾向於使用SET XACT_ABORT = ON很多。Erland Sommarskog 在 SQL Server 社區中非常受推崇，但我不同意這一點：

儲存過程應始終在開頭包含此語句：

SET XACT_ABORT, NOCOUNT ON

引用自：https://dba.stackexchange.com/questions/257028