The Maximum Recursion 100 Has Been Exhausted Before Statement Completion

The maximum recursion 100 has been exhausted before statement completion

Specify the maxrecursion option at the end of the query:

...
from EmployeeTree
option (maxrecursion 0)

That allows you to specify how often the CTE can recurse before generating an error. Maxrecursion 0 allows infinite recursion.

The maximum recursion 100 has been exhausted

This behavior is described in the documentation:

To prevent an infinite loop, you can limit the number of recursion levels allowed for a particular statement by using the MAXRECURSION hint and a value between 0 and 32,767 in the OPTION clause of the INSERT, UPDATE, DELETE, or SELECT statement. This lets you control the execution of the statement until you resolve the code problem that is creating the loop. The server-wide default is 100. When 0 is specified, no limit is applied.

You have hit the default limit of 100 iterations (which gives you a little more than 3 months of data).

The way your query is built, there is no risk of infinite loop. So, you can just allow an unlimited number of iterations by adding option (maxrecursion 0) at the end of your query.

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

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.

The statement terminated. The maximum recursion 100 has been exhausted before statement completion

By default Recursive CTE will do 100 recursion.

Add option max recursion at the end of cte to increase the default recursion. But there are logical mistakes are there as well in your query. If am not wrong you are looking for something like this

;WITH Rec (AddrPartId, ParentID, AddrPartTypeId, NAME)
     AS (SELECT AddrPartId,
                ParentID,
                AddrPartTypeId,
                NAME
         FROM   addressparts
         WHERE  ParentID = 0 -- add the parent id filter here 
         UNION ALL
         SELECT A.AddrPartId,  -- don't select from recursive cte 
                A.ParentID,
                A.AddrPartTypeId,
                A.NAME
         FROM   Rec
                INNER JOIN addressparts A
                        ON Rec.AddrPartId = A.ParentID)
SELECT *
FROM   Rec
OPTION (maxrecursion 0); -- here

• Live Demo

SQL Server : the maximum recursion 100 has been exhausted before statement completion

Usually, At the end of the select statement that uses the recurcive cte.

However, inside a view that will not work.
A quick search got me to this post - that explains the correct way to do it.

Turns out you can't use the query hint inside the view, but you can and should use it in the query that calls the view.

Sample table:

CREATE TABLE T
(
    id int,
    parent int
)
INSERT INTO T VALUES (1, NULL), (2, 1), (3, 1), (4, 2), (5, 2), (6, 3), (7, 4), (8, 5);
GO

Create the view:

CREATE VIEW V
AS

WITH CTE AS
(
    SELECT id, parent
    FROM T 
    WHERE parent IS NULL
    UNION ALL

    SELECT t.id, t.parent
    FROM T
    INNER JOIN CTE ON t.parent = cte.id
)

SELECT *
FROM CTE

GO

Execute the view:

SELECT *
FROM V
OPTION (MAXRECURSION 2);

The statement terminated. The maximum recursion 100 has been exhausted before statement completion error

The specific fix to your problem is to add option (maxrecursion) to the end of the query:

select *
from cte 
option (maxrecursion 0);

This assumes that the CTE is behaving correctly. You are generating rows by month, and 100 months is a fair number of years. If you are not expecting time periods of more than 12 years, then you might have a bug in your code.

CTE Query. The statement terminated. The maximum recursion 100 has been exhausted before statement completion

The error caused default recursion was 100, but your query might more than that, you can try to add this hint on the end of a query

The server-wide default is 100. When 0 is specified, no limit is applied. Only one MAXRECURSION value can be specified per statement. For more information

I would set a number for that instead of 0 if you have expected the most recursive deep.

WITH rCTE AS(
    SELECT H.Person,
           H.AssignedTo,
           H.Person AS NextPerson,
           H.AssignedTo AS NextAssignedTo,
           CONVERT(nvarchar(4000),CONCAT('|',H.AssignedTo,'|')) AS ExpectedResult,
           1 AS Level
    FROM dbo.tblHierarchy H
    UNION ALL      
    SELECT r.Person,
           r.AssignedTo,
           H.Person AS NextPerson,
           H.AssignedTo AS NextAssignedTo,
           CONVERT(nvarchar(4000),CONCAT(r.Expectedresult,H.AssignedTo,'|')),
           Level + 1
    FROM dbo.tblHierarchy H
         JOIN rCTE r ON r.NextAssignedTo = H.Person),
RNs AS(
    SELECT r.Person,
           r.AssignedTo,
           r.ExpectedResult,
           ROW_NUMBER() OVER (PARTITION BY r.Person ORDER BY r.level DESC) AS RN
    FROM rCTE r)
SELECT RN.Person,
       RN.AssignedTo,
       RN.ExpectedResult
FROM RNs RN
WHERE RN = 1
ORDER BY RN.Person
option (maxrecursion 0);

---

Related Topics