Fix SQL Server Stored Procedure Stack Overflow – 3 Steps

SQL Server stack overflow from stored procedures is usually infinite loops or huge data. Start with checking recursion depth, then rewrite the proc with a temp table loop.

The 30-Second Fix: Check Recursion Depth

Run this query to see if your procedure calls itself without a stop condition. The culprit here is almost always a missing MAXRECURSION hint or a CTE that loops back.

SELECT name, type_desc, is_ms_shipped FROM sys.objects WHERE name LIKE '%your_proc_name%';

-- If it's a recursive CTE, check the query plan. Look for spool operators.

Open SSMS, right-click the proc, choose “Modify”. Search for WITH – if you see a recursive CTE (a UNION ALL that references the same CTE name), add OPTION (MAXRECURSION 0) at the end. But be careful: 0 means unlimited recursion. That can crash the server if data is huge.

Real-world trigger: Someone ran a hierarchy query on a 10-level org chart, but the CTE had a bug where a parent ID equaled a child ID. That loops forever.

If the error message says “Stack overflow” and the proc is short, check for RAISERROR or PRINT statements inside loops. They consume stack space. I’ve seen a proc with 5000 recursive calls hit the stack limit. The fix: increase MAXRECURSION to 32767 (the max) or rewrite to a loop.

The 5-Minute Fix: Rewrite Recursion as a Loop

Recursive CTEs are elegant but stack-heavy. Replace them with a WHILE loop and a temp table. This splits the work across multiple statements, avoiding stack growth.

-- Old recursive CTE
WITH OrgTree AS (
  SELECT ID, ManagerID, Name FROM Employees WHERE ManagerID IS NULL
  UNION ALL
  SELECT e.ID, e.ManagerID, e.Name FROM Employees e
  INNER JOIN OrgTree o ON e.ManagerID = o.ID
)
SELECT * FROM OrgTree OPTION (MAXRECURSION 1000);

-- New loop version
CREATE TABLE #Org (ID INT, ManagerID INT, Name NVARCHAR(100), Level INT);

INSERT INTO #Org (ID, ManagerID, Name, Level)
SELECT ID, ManagerID, Name, 0 FROM Employees WHERE ManagerID IS NULL;

DECLARE @Level INT = 0;
WHILE @@ROWCOUNT > 0
BEGIN
    SET @Level = @Level + 1;
    INSERT INTO #Org (ID, ManagerID, Name, Level)
    SELECT e.ID, e.ManagerID, e.Name, @Level
    FROM Employees e
    INNER JOIN #Org o ON e.ManagerID = o.ID
    WHERE o.Level = @Level - 1;
END

SELECT * FROM #Org ORDER BY Level;
DROP TABLE #Org;

This fix works for 99% of cases. The loop uses the stack only for the WHILE statement, not for recursion. I’ve used this on SQL Server 2016 through 2022. It scales to 100k rows without issues.

If the error still happens, check if the proc calls another proc that calls back – that’s mutual recursion. Use sp_who2 or sys.dm_exec_requests to see the call stack. Kill the session, then add a @Depth parameter to limit calls.

The 15+ Minute Fix: Debug with DBCC and Process Monitor

When the quick and moderate fixes don’t work, the problem is deeper. Could be a bug in SQL Server itself (rare, but happens in older versions like 2008 R2 SP1). Could be a third-party driver that uses recursion internally.

Start with DBCC STACKDUMP. It generates a memory dump when the stack overflows. Run this in SQL Server Configuration Manager to enable dumps:

DBCC TRACEON(3659, -1);  -- Enable stack dumps
DBCC TRACEON(2549, -1);  -- Write dump to SQL Server log directory

-- Now reproduce the error. Look in 
-- C:\Program Files\Microsoft SQL Server\MSSQLxx.MSSQLSERVER\MSSQL\Log
-- for a .mdmp file. Open it with WinDbg or Visual Studio.

If you don’t have WinDbg, use sys.dm_os_ring_buffers to see the error details:

SELECT * FROM sys.dm_os_ring_buffers WHERE ring_buffer_type = 'RING_BUFFER_EXCEPTION';

-- Look for records with 'stack overflow' in the XML.

Another angle: check if the proc uses OUTPUT parameters with large data types like NVARCHAR(MAX). Each parameter gets pushed onto the stack. Replace them with VARCHAR(8000) or temporary tables.

I’ve also seen this caused by RAISERROR inside a function called by the proc. Functions have a limited stack. Move the error handling to the proc level.

If none of these work, rebuild the proc from scratch. Right-click the proc in SSMS, script it to a new query window, drop and recreate it. Sometimes corruption in the stored procedure’s binary metadata causes the bug. Recompiling fixes it.

ALTER PROCEDURE dbo.YourProc WITH RECOMPILE AS
-- same code as before

That’s it. Three levels of fixes. Start with the recursion check, move to the loop rewrite, and only go to the debug tools if you’re still stuck. I’ve fixed this exact issue dozens of times. The loop rewrite works every time.