Deleting Rows in Batches in SQL Server

A DELETE that removes hundreds of thousands of rows in one go is the one that fills the transaction log, locks the whole table and leaves everything else waiting. Delete in batches instead: small transactions whose log space can be reused, and a lock footprint other sessions can work around. Here is the loop, and what to watch while it runs.

The Batch Delete Loop

Each pass deletes up to 5,000 qualifying rows and commits. The loop stops on the first pass that deletes nothing.

-- Delete data in batches
DECLARE @BatchSize INT = 5000;

WHILE 1 = 1
BEGIN
    DELETE TOP (@BatchSize)
    FROM dbo.JupiterHarvest
    WHERE HarvestDate < '2023-01-01';

    IF @@ROWCOUNT = 0
        BREAK;
END;

The Messages tab shows one line per batch, ending on the empty batch that breaks the loop:

Delete the Oldest Rows First

DELETE TOP takes whichever qualifying rows SQL Server reaches first. The DELETE page in MS Docs says it plainly: the rows referenced in the TOP expression are not arranged in any order. If a retention job might be stopped part way, you want the oldest rows gone first.

DELETE has no ORDER BY of its own. Add one and the batch fails to parse with Msg 156, Incorrect syntax near the keyword 'ORDER'. Put the ORDER BY in a CTE and delete from the CTE:

-- Oldest rows first: the ORDER BY goes in a CTE, not on the DELETE
DECLARE @BatchSize INT = 5000;

;WITH Batch AS (
    SELECT TOP (@BatchSize) *
    FROM dbo.JupiterHarvest
    WHERE HarvestDate < '2023-01-01'
    ORDER BY HarvestDate
)
DELETE FROM Batch;

Order costs reads. With no index on HarvestDate, every batch scans the whole table and sorts it: 24,616 logical reads per batch on a 600,000 row table, first batch to last. An index on the column removes the sort, but every deleted row then has to come out of the index too, and the same delete read about 32,000 per batch. Use the CTE when the order matters, not by default.

Pause Between Batches, After the Check

A short WAITFOR DELAY between batches gives log backups and other sessions room. Where it goes matters more than how long it is: WAITFOR sets @@ROWCOUNT to 0, so a delay between the DELETE and the check ends the loop after the first batch. Nothing errors, and the script looks like it finished. Put the delay at the bottom of the loop:

-- Throttled batch delete: the delay goes AFTER the @@ROWCOUNT check
DECLARE @BatchSize INT = 5000;

WHILE 1 = 1
BEGIN
    DELETE TOP (@BatchSize)
    FROM dbo.JupiterHarvest
    WHERE HarvestDate < '2023-01-01';

    IF @@ROWCOUNT = 0
        BREAK;

    WAITFOR DELAY '00:00:01';
END;

With the delay above the check instead, a loop of 1,000 row batches deleted 1,000 rows and stopped:

What It Does to the Transaction Log

Batching limits how much log one transaction needs. Whether that space is reused depends on the recovery model: MS Docs on log truncation puts it at the next checkpoint under SIMPLE, and only after a log backup under FULL. Deleting 500,000 of 600,000 rows from a 65 MB table, with the log starting at 8 MB:

• One DELETE needed about 417 MB of log under either model, more than 6 times the size of the table. With the log capped at 360 MB it failed with Msg 9002, log full due to ACTIVE_TRANSACTION, and rolled back. That is the ACTIVE_TRANSACTION case of a full log.
• Batches under SIMPLE held the log at 48 MB, because checkpoints free the space between batches.
• Batches under FULL with no log backup still grew it to 152 MB, and all 148.3 MB stayed in use with log_reuse_wait_desc at LOG_BACKUP. With a log backup every 10 batches it stayed at 40 MB.

So on a FULL database, confirm log backups are running and succeeding before you start, not just scheduled. SQL Server Recovery Models Explained covers what FULL commits you to, and Transaction Log Size and Usage shows the log filling while the loop runs. If the disk is already full, Disk Is Full or the Log Will Not Truncate is the place to start.

Lock Escalation and Batch Size

SQL Server swaps row locks for one table lock once a single statement holds about 5,000 locks on one table or index (MS Docs on lock escalation). From then until your DELETE commits, every other reader and writer on that table waits, unless it reads row versions under RCSI or snapshot isolation.

One DELETE of 200,000 rows escalated to an exclusive table lock, and another session reading a row the DELETE never touched gave up with Msg 1222, Lock request time out period exceeded. The same 200,000 rows deleted in batches of 5,000 or 4,000 never escalated, so Get-LockEscalationStats lists only the single DELETE:

Each 5,000 row batch held 5,000 key locks and 68 page locks without escalating; a 10,000 row batch took the table lock. Treat 5,000 as a ceiling, not a target, because every extra index on the table adds locks for each row deleted.

When the Date Column Has No Index

With no index to seek on, each DELETE TOP batch scans from the start of the table to find its rows, past the space earlier batches already cleared. With 300,000 old rows spread through a 600,000 row table, the reads per batch climbed steadily:

Walk the clustered key in ranges instead. Each pass reads only its own slice of the table:

-- Batch by clustered key range when the date column has no index
DECLARE @BatchSize INT = 5000,
        @FromId    INT = 0,
        @MaxId     INT = (SELECT MAX(HarvestId) FROM dbo.JupiterHarvest);

WHILE @FromId < @MaxId
BEGIN
    DELETE FROM dbo.JupiterHarvest
    WHERE HarvestId > @FromId
      AND HarvestId <= @FromId + @BatchSize
      AND HarvestDate < '2023-01-01';

    SET @FromId += @BatchSize;
END;

On the same table that took 19,523 logical reads in total, against 265,715 for the DELETE TOP loop. A range can hold fewer than 5,000 old rows, or none, so this loop ends at the highest key rather than on an empty batch.

Frequently Asked Questions