SQL Server Error 530

Error number

530

Severity

16 (user-correctable error)

State

1

Product

Microsoft SQL Server Database Engine

Versions

All supported SQL Server versions (2016 to 2025), Azure SQL Database, Azure SQL Managed Instance

Category

Query Processing & Performance

Last reviewed

23 October 2026

What it means

Error 530 means a recursive common table expression (CTE) called itself more times than the recursion limit allows, so SQL Server stopped the statement.

A recursive CTE has an anchor query and a recursive member that refers back to the CTE. Each pass of the recursive member is one level of recursion. To protect the server from runaway queries, SQL Server stops after 100 levels by default and raises 530 at severity 16. The number in the message is the limit that was in force: 100 when the query has no hint, or the value of OPTION (MAXRECURSION n) when it has one.

The error has two very different causes, and the fix depends on which one you have. Either the data really is deeper than the limit (a calendar of 365 days, a number series, a deep bill of materials), and raising the limit for that query is correct. Or the data contains a loop, such as an employee who is their own manager two levels up, and the recursion would never end at any limit. Rows already streamed to the client before the error are not a complete result.

Common causes

• A number or date generator that needs more than 100 levels, for example one row per day for a year.
• A hierarchy (organisation chart, category tree, bill of materials) that is deeper than 100 levels or deeper than the MAXRECURSION value in the query.
• A cycle in parent and child data: A reports to B, B reports to C and C reports to A, so the recursive member never runs out of rows.
• A recursive member whose stop condition is wrong or missing, for example comparing the wrong column or never incrementing a counter.

Diagnose it

Read-only These queries only read metadata and dynamic management views. They do not change data, permissions or configuration, so they are safe to run first.

WITH chain AS (
    SELECT id, manager_id, 0 AS lvl
    FROM dbo.Employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.manager_id, c.lvl + 1
    FROM dbo.Employees AS e
    JOIN chain AS c ON e.manager_id = c.id
    WHERE c.lvl < 1000
)
SELECT MAX(lvl) AS deepest_level, COUNT(*) AS rows_reached
FROM chain
OPTION (MAXRECURSION 1000);

WITH walk AS (
    SELECT id AS start_id, manager_id,
           CAST(CONCAT('/', id, '/') AS varchar(4000)) AS path, 0 AS is_cycle
    FROM dbo.Employees
    UNION ALL
    SELECT w.start_id, e.manager_id,
           CAST(CONCAT(w.path, e.id, '/') AS varchar(4000)),
           CASE WHEN w.path LIKE CONCAT('%/', e.id, '/%') THEN 1 ELSE 0 END
    FROM walk AS w
    JOIN dbo.Employees AS e ON e.id = w.manager_id
    WHERE w.is_cycle = 0
)
SELECT start_id, path
FROM walk
WHERE is_cycle = 1
OPTION (MAXRECURSION 1000);

SELECT id, name, manager_id
FROM dbo.Employees
WHERE manager_id = id;

How to fix it

Each fix is labelled with what it changes. Try read-only checks and the least invasive fix first, and test changes outside production.

WITH days AS (
    SELECT CAST('2026-01-01' AS date) AS d
    UNION ALL
    SELECT DATEADD(day, 1, d) FROM days WHERE d < '2026-12-31'
)
SELECT COUNT(*) AS days_in_year
FROM days
OPTION (MAXRECURSION 366);

SELECT DATEADD(day, value, CAST('2026-01-01' AS date)) AS d
FROM GENERATE_SERIES(0, 364);

WITH chain AS (
    SELECT id, manager_id, CAST(CONCAT('/', id, '/') AS varchar(4000)) AS path
    FROM dbo.Employees
    WHERE id = 1
    UNION ALL
    SELECT e.id, e.manager_id, CAST(CONCAT(c.path, e.id, '/') AS varchar(4000))
    FROM dbo.Employees AS e
    JOIN chain AS c ON e.id = c.manager_id
    WHERE c.path NOT LIKE CONCAT('%/', e.id, '/%')
)
SELECT id, manager_id, path
FROM chain;

UPDATE dbo.Employees
SET manager_id = NULL      -- Sara is the top of the chart
WHERE id = 1;

ALTER TABLE dbo.Employees
ADD CONSTRAINT CK_Employees_not_own_manager CHECK (manager_id <> id);

WITH numbers AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM numbers WHERE n < 50000
)
SELECT MAX(n) AS highest
FROM numbers
OPTION (MAXRECURSION 0);

Examples

A number series longer than the default limit of 100

-- Generate the numbers 1 to 200 with a recursive CTE
WITH numbers AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM numbers WHERE n < 200
)
SELECT MAX(n) AS highest
FROM numbers;

Msg 530, Level 16, State 1, Line 2
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

One row per day for a year

WITH days AS (
    SELECT CAST('2026-01-01' AS date) AS d
    UNION ALL
    SELECT DATEADD(day, 1, d) FROM days WHERE d < '2026-12-31'
)
SELECT COUNT(*) AS days_in_year
FROM days;   -- needs 364 levels

Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

A loop in the data: raising the limit does not help

SET NOCOUNT ON;
CREATE TABLE dbo.Employees (id INT PRIMARY KEY, name VARCHAR(50), manager_id INT);
-- Sara reports to Omar, Omar to Ali, Ali to Sara: a cycle
INSERT INTO dbo.Employees VALUES (1, 'Sara', 3), (2, 'Ali', 1), (3, 'Omar', 2);

WITH chain AS (
    SELECT id, manager_id FROM dbo.Employees WHERE id = 1
    UNION ALL
    SELECT e.id, e.manager_id
    FROM dbo.Employees AS e
    JOIN chain AS c ON e.id = c.manager_id
)
SELECT COUNT(*) AS managers_above_sara
FROM chain
OPTION (MAXRECURSION 1000);

Msg 530, Level 16, State 1, Line 6
The statement terminated. The maximum recursion 1000 has been exhausted before statement completion.

How to prevent it

Decide the expected depth when you write a recursive CTE and put a matching MAXRECURSION hint on the statement, so a surprise in the data fails loudly at a known point instead of at the default of 100. For number and date series, prefer GENERATE_SERIES or a permanent numbers or calendar table over recursion.

Protect hierarchies at write time: a CHECK that a row is not its own parent, and application or trigger logic that refuses a new parent which is already a descendant. For practice with recursive queries, try the recursive employee hierarchy exercise.

Common questions

Learn more on CodeWithSQL

• Recursive employee hierarchy (exercise)
• DATEADD()
• Advanced SQL functions

Official documentation

• WITH common_table_expression (Transact-SQL)
• Query hints: MAXRECURSION (Transact-SQL)
• Database Engine events and errors 0 to 999

Last technically reviewed 23 October 2026; examples and diagnostics run on SQL Server 2025 (17.0.1145.4). Message text checked against the engine's sys.messages catalogue.