WHILE 1 = 1 Loop in T-SQL: Run Forever, Stop Safely

A WHILE 1 = 1 loop never ends on its own, so every one needs a planned way out. The condition is always true. That is the right shape for a sampler that runs until you say stop. It is the wrong shape for anything without an exit.

When You Need One

Some problems appear at random. A query stalls twice a day, and no one is watching when it happens. The cure is a small sampler that records the state of the server every second until the trouble shows up. It has no natural end, so the loop has none either.

The simplest WHILE 1 = 1 loop is two lines. It is also the most dangerous. This loop prints 1 forever, and it will take the whole connection until someone cancels it. Do not run it.

WHILE 1 = 1
    SELECT 1;

A real sampler does a costly query inside the loop. With no exit, that query runs until the server runs out of room. So the work starts with the exits.

When the loop has a natural end, write that end in the condition: WHILE @round < @maxRounds. Use WHILE 1 = 1 only when the end is decided inside the loop.

Build the Sampler With Three Ways Out

The demo database is LoopGuardDemo. One table holds a stop flag, and the other holds the samples. Run it on a test server.

IF DB_ID(N'LoopGuardDemo') IS NULL CREATE DATABASE LoopGuardDemo;
GO
USE LoopGuardDemo;
GO
DROP TABLE IF EXISTS dbo.LoopControl;
DROP TABLE IF EXISTS dbo.LoopLog;
CREATE TABLE dbo.LoopControl (StopNow bit NOT NULL);
INSERT INTO dbo.LoopControl (StopNow) VALUES (0);
CREATE TABLE dbo.LoopLog (
    LogRound   int NOT NULL,
    CapturedAt datetime2(3) NOT NULL,
    Requests   int NOT NULL
);

The loop below checks three exits before each round. The first is the stop flag, which another window can set. The second is a round limit. The third is a time limit. Whichever fires first ends the loop. For the demo the round limit is 3, so the loop finishes in about three seconds.

DECLARE @round int = 0, @started datetime2 = SYSDATETIME();
DECLARE @maxRounds int = 3, @maxSeconds int = 30;
WHILE 1 = 1
BEGIN
    IF EXISTS (SELECT 1 FROM dbo.LoopControl WHERE StopNow = 1) BREAK;
    IF @round >= @maxRounds OR DATEDIFF(second, @started, SYSDATETIME()) >= @maxSeconds BREAK;
    SET @round += 1;
    INSERT INTO dbo.LoopLog (LogRound, CapturedAt, Requests)
    SELECT @round, SYSDATETIME(), COUNT(*) FROM sys.dm_exec_requests WHERE session_id <> @@SPID;
    RAISERROR(N'Round %d captured', 0, 1, @round) WITH NOWAIT;
    WAITFOR DELAY '00:00:01';
END;

Each pass takes one sample, prints a message and waits one second. The message uses severity 0 and WITH NOWAIT. It appears at once, not at the end of the batch. This query reads back what the loop recorded.

SELECT COUNT(*) AS RoundsLogged,
       CAST(ROUND(DATEDIFF(millisecond, MIN(CapturedAt), MAX(CapturedAt)) / 1000.0, 0) AS int) AS SecondsFirstToLast
FROM dbo.LoopLog;

Three samples, two seconds apart from first to last. The Requests column holds the number of active requests at each moment. It changes on every run, so it is not shown here. In a real sampler, this is where you put the diagnostic query you need.

Stop It From Another Window

For a real run, set the round limit high or remove it. Keep the time limit as a safety net. Then end the WHILE 1 = 1 loop on purpose from a second window. This statement sets the stop flag. The loop sees it before its next round and leaves cleanly.

UPDATE LoopGuardDemo.dbo.LoopControl SET StopNow = 1;

In a test with the round limit removed, the flag was set after about four seconds. The loop stopped after four rounds. To repeat it, set @maxRounds to 1000 and run the loop again. Reset the flag to 0 before the next run.

A loop with no flag needs a harder stop. In SSMS you can cancel the query. From another window you can use KILL with the session id. That needs the ALTER ANY CONNECTION permission or the sysadmin role. The killed session receives an error: Cannot continue the execution because the session is in the kill state. Work done inside an open transaction is rolled back, which can take time. The flag is gentler, so build it in.

Loops That Hide in a Recursive CTE

A recursive CTE is a loop too. SQL Server stops it at 100 levels by default. This query counts to 150 and hits the limit.

WITH Counter AS (SELECT 1 AS n UNION ALL SELECT n + 1 FROM Counter WHERE n < 150)
SELECT MAX(n) AS Biggest FROM Counter;

SQL Server stops it with Msg 530, a level 16 error. The text reads as follows.

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

The limit is a guard. Raise it to the size you expect, and not beyond. This version asks for 200 levels and returns 150.

WITH Counter AS (SELECT 1 AS n UNION ALL SELECT n + 1 FROM Counter WHERE n < 150)
SELECT MAX(n) AS Biggest FROM Counter OPTION (MAXRECURSION 200);

MAXRECURSION 0 removes the limit. A CTE with a mistake in its stop condition then runs until the server gives up. Keep the guard on unless you know the depth.

Loop or Job

You could argue that a SQL Server Agent job every minute is simpler than a loop. For a minute it is, because the Agent handles restarts and keeps history. A loop wins when you need samples every second, or when the sampler must keep local state between rounds. Pick the Agent for slow rhythms and the loop for fast ones.

What to Remember

Write the exits of every WHILE 1 = 1 loop before the work. A stop flag, a round limit and a time limit cost five lines and save a server. Test the sampler with a limit of three rounds before you let it run for hours.

When you finish the demo, drop the database.

USE master;
GO
DROP DATABASE LoopGuardDemo;

A loop that never ends is not a design, it is a promise to stop it yourself.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.