The maximum recursion 32767 has been exhausted before statement completion. [SQLSTATE 42000] (Error 530)

I am using below query to identify blocking on server and send an alert also kill the session (executing via SQL Job). Query is using 2 CET table Blcokers and Hierarchy.

Many times I am getting below error and the job fails.

Error: NT SERVICE\SQLSERVERAGENT. The statement terminated. The maximum recursion 32767 has been exhausted before statement completion. [SQLSTATE 42000] (Error 530). The step failed.

On research I found to use OPTION(MAXRECURSION) HINT in the query to avid the error. I have included the HINT for Hierarchy CET table as per below but still getting same error

SELECT * INTO #BlockingProcess FROM Hierarchy OPTION(MAXRECURSION 32767)

Can some one please suggest me what changes should I make in the below code to stop the infinite recursion.

ENITER CODE:

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

--SELECT SPID, BlkBy FROM #sp_who2
DELETE FROM #sp_who2
 WHERE BlkBy='  .'
   AND SPID NOT IN (SELECT BlkBy FROM #sp_who2 WHERE BlkBy IS NOT NULL)

;WITH Hierarchy(ChildSPID,Generation,BlkBy)
AS
(
    SELECT SPID, 0, BlkBy
      FROM #sp_who2 AS FirtGeneration
     WHERE BlkBy='  .'       
     UNION ALL
    SELECT NextGeneration.SPID, Parent.Generation+1, Parent.ChildSPID
      FROM #sp_who2 AS NextGeneration
            INNER JOIN Hierarchy AS Parent ON NextGeneration.BlkBy = Parent.ChildSPID    
)
SELECT * INTO #BlockingProcess
  FROM Hierarchy
OPTION(MAXRECURSION 32767)

SELECT * FROM #BlockingProcess

--loop and kill lead blockers
DECLARE @SPIDGen0           INT
DECLARE @SPIDGen1           INT
DECLARE @ElapsedTimeMSGen0  INT  --if NULL, use Gen1
DECLARE @ElapsedTimeMSGen1  INT
DECLARE @SUBJECTKILL            VARCHAR(200);
--DECLARE @tableHTMLKILL            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 = BlockingRequest.total_elapsed_time
      FROM sys.dm_exec_requests BlockingRequest
     WHERE session_id=@SPIDGen0

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

--If (select count(*) from #BLOCKERS) >= 1
IF ISNULL(@ElapsedTimeMSGen0,@ElapsedTimeMSGen1) >= 120000
begin
DECLARE @Subject varchar(100)
SELECT @Subject = 'Blocking Tree Report from ' +  @@servername
    EXEC msdb.dbo.sp_send_dbmail @body = @tableHTML
        ,@body_format = 'HTML'
        ,@profile_name = N''
        ,@recipients = N''
       
        ,@Subject = @Subject      
end

drop table #BLOCKERS

WAITFOR DELAY '00:03'

IF ISNULL(@ElapsedTimeMSGen0,@ElapsedTimeMSGen1)>180000 --milliseconds = 3 minutes
    --IF ISNULL(@ElapsedTimeMSGen0,@ElapsedTimeMSGen1)>60000 --milliseconds = 3 minutes
    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
    --Skip current SPID and move to next 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

Use sp_whoisactive instead. It gives pretty human-readable view of what is going on and what process is process or blocking which others.

Also, you can filter the results by running time and kill the slowest.

But, what you are doing does not seem right. You have automation script for killing transaction. What if one is generated invoice report once per month which take 5 minutes and you constantly kill it? What if one is creating an index which blocks transactions on the specified table and you kill it?

There is a reason there is no such functionality by Microsoft - a person needs to take the call.

It will be better to use this routine to identify slow queries and fix them.