SQL递归耗尽报错求助：添加MAXRECURSION仍报错的代码修改建议

问题背景 #

我运行一个SQL作业来识别服务器阻塞、发送告警并终止阻塞会话，作业里用了两个CTE（Blockers和Hierarchy），但频繁失败，报错信息如下：

NT SERVICE\SQLSERVERAGENT。语句已终止。在语句完成前已耗尽最大递归深度32767。[SQLSTATE 42000] (错误530)。步骤失败。

我尝试给Hierarchy CTE加上OPTION(MAXRECURSION 32767)提示，但还是触发同样的错误，希望修改代码彻底停止无限递归。

错误原因分析 #

出现无限递归的核心问题有这几个：

• 递归CTE的连接条件可能产生循环引用（比如某个会话的BlkBy指向下游会话，形成闭环）
• 原始的#sp_who2临时表清理逻辑不够严谨，残留了无效的阻塞关系数据
• 递归过程没有做防循环的检测机制

修改后的完整代码 #

SET NOCOUNT ON;
SET CONCAT_NULL_YIELDS_NULL OFF
GO

CREATE TABLE #sp_who2(
 ID INT IDENTITY(1,1) NOT NULL,
 SPID VARCHAR(4),
 Status VARCHAR(200),
 Login VARCHAR(200),
 HostName VARCHAR(200),
 BlkBy VARCHAR(4),
 DBName VARCHAR(200),
 Command VARCHAR(200),
 CPUTime VARCHAR(20),
 DiskIO VARCHAR(20),
 LastBatch VARCHAR(20),
 ProgramName VARCHAR(200),
 SPID2 VARCHAR(4),
 RequestID VARCHAR(4)
)

INSERT #sp_who2 EXEC sp_who2

-- 更严谨的清理：只保留有效阻塞关系，排除自我阻塞/无效记录
DELETE FROM #sp_who2 
WHERE 
    (BlkBy = ' .' AND SPID NOT IN (SELECT BlkBy FROM #sp_who2 WHERE BlkBy <> ' .' AND BlkBy IS NOT NULL))
    OR SPID = BlkBy -- 排除自我阻塞的无效记录
    OR BlkBy IS NULL

;WITH Hierarchy(ChildSPID, Generation, BlkBy, Path) AS (
    -- 起始条件：只取真正的顶级阻塞者
    SELECT 
        SPID, 
        0, 
        BlkBy,
        CAST('/' + SPID + '/' AS VARCHAR(MAX)) -- 记录递归路径，用于检测循环
    FROM #sp_who2 AS FirstGeneration 
    WHERE BlkBy = ' .'

    UNION ALL

    SELECT 
        NextGeneration.SPID, 
        Parent.Generation + 1, 
        Parent.ChildSPID,
        Parent.Path + NextGeneration.SPID + '/' -- 追加当前SPID到路径中
    FROM #sp_who2 AS NextGeneration 
    INNER JOIN Hierarchy AS Parent 
        ON NextGeneration.BlkBy = Parent.ChildSPID
    -- 关键：防止循环递归，检查当前SPID是否已在路径中
    AND CHARINDEX('/' + NextGeneration.SPID + '/', Parent.Path) = 0
)
SELECT * INTO #BlockingProcess FROM Hierarchy 
OPTION(MAXRECURSION 32767)

SELECT * FROM #BlockingProcess

-- 循环处理并终止顶级阻塞者
DECLARE @SPIDGen0 INT
DECLARE @SPIDGen1 INT
DECLARE @ElapsedTimeMSGen0 INT -- 如果为空，使用Gen1的时间
DECLARE @ElapsedTimeMSGen1 INT
DECLARE @SUBJECTKILL VARCHAR(200);
DECLARE @tableHTML NVARCHAR(MAX); -- 补充原代码未声明的变量

WHILE EXISTS(SELECT * FROM #BlockingProcess WHERE BlkBy = ' .')
BEGIN
    SELECT @SPIDGen0 = MIN(ChildSPID) FROM #BlockingProcess WHERE Generation = 0
    SELECT @SPIDGen1 = MIN(ChildSPID) FROM #BlockingProcess WHERE Generation = 1 AND BlkBy = @SPIDGen0

    PRINT @SPIDGen0
    PRINT @SPIDGen1

    SELECT @ElapsedTimeMSGen0 = total_elapsed_time FROM sys.dm_exec_requests WHERE session_id = @SPIDGen0
    SELECT @ElapsedTimeMSGen1 = total_elapsed_time FROM sys.dm_exec_requests WHERE session_id = @SPIDGen1

    PRINT @ElapsedTimeMSGen0
    PRINT @ElapsedTimeMSGen1

    -- 阻塞时间超过2分钟（120000毫秒）时发送告警
    IF ISNULL(@ElapsedTimeMSGen0, @ElapsedTimeMSGen1) >= 120000
    BEGIN
        DECLARE @Subject VARCHAR(100)
        SELECT @Subject = 'Blocking Tree Report from ' + @@SERVERNAME
        
        -- 生成简单的阻塞树HTML报表（可根据需求自定义）
        SET @tableHTML = N'<H1>Blocking Tree Report</H1>' +
                         N'<table border="1">' +
                         N'<tr><th>Child SPID</th><th>Generation</th><th>Blocked By</th></tr>' +
                         CAST((
                             SELECT td = ChildSPID, '',
                                    td = Generation, '',
                                    td = BlkBy
                             FROM #BlockingProcess
                             WHERE BlkBy = ' .' OR BlkBy = @SPIDGen0
                             FOR XML PATH('tr'), TYPE
                         ) AS NVARCHAR(MAX)) +
                         N'</table>'

        EXEC msdb.dbo.sp_send_dbmail 
            @body = @tableHTML,
            @body_format = 'HTML',
            @profile_name = N'', -- 替换为你的邮件配置文件名称
            @recipients = N'', -- 替换为收件人邮箱
            @Subject = @Subject
    END

    -- 阻塞时间超过3分钟（180000毫秒）时终止会话
    IF ISNULL(@ElapsedTimeMSGen0, @ElapsedTimeMSGen1) > 180000
    BEGIN
        SELECT @SUBJECTKILL = @@SERVERNAME + ' - Lead Blocker Session ' + CAST(@SPIDGen0 AS VARCHAR(5)) + ' Killed'
        
        EXEC msdb.dbo.sp_send_dbmail 
            @profile_name = '', -- 替换为你的邮件配置文件名称
            @recipients = '', -- 替换为收件人邮箱
            @subject = @SUBJECTKILL,
            @body = @tableHTML,
            @body_format = 'HTML'

        EXEC('KILL ' + @SPIDGen0)
    END

    -- 跳过当前SPID，处理下一个
    DELETE FROM #BlockingProcess WHERE ChildSPID = @SPIDGen0
END

-- 清理临时表
IF OBJECT_ID('tempdb..#sp_who2') IS NOT NULL DROP TABLE #sp_who2
IF OBJECT_ID('tempdb..#BlockingProcess') IS NOT NULL DROP TABLE #BlockingProcess

关键修改点说明 #

• 添加循环检测机制：在Hierarchy CTE中新增Path字段记录递归路径，递归步骤中通过CHARINDEX检查当前SPID是否已在路径内，彻底避免无限循环
• 优化临时表清理逻辑：删除自我阻塞（SPID = BlkBy）和无效的阻塞记录，确保递归起始数据的准确性
• 补充未声明变量：原代码中@tableHTML未声明，补充定义并添加了基础HTML报表生成逻辑（可按需自定义）
• 强化起始条件过滤：确保起始的根阻塞者是真正的顶级阻塞会话，避免引入无效递归起点

内容的提问来源于stack exchange，提问作者Aditya Sawant