Safe batched DELETE in SQL Server, with a dry run

By Scott Coffey, Senior Database Developer. Updated 2026-10-15.

A single DELETE FROM dbo.EventLog WHERE EventAt < @Cutoff on a large table holds locks for the whole statement, can escalate to a table lock, and writes every deleted row to the transaction log in one transaction. If it fails or is cancelled after 40 minutes, the rollback can take as long again. Batching fixes this by doing the same work as many small transactions.

The pattern

Dry run by default. The first run only counts what would be deleted.
Small batches. DELETE TOP (@BatchSize), each in its own short transaction. Keep the batch under about 5,000 rows; SQL Server attempts lock escalation at roughly 5,000 locks on one object in one statement.
A hard cap. @MaxRows stops the run even if the predicate is wrong.
A pause. WAITFOR DELAY between batches lets other sessions and log backups get through.
Logging. Each batch writes a row to a log table so you can see progress from another session.

The procedure

This follows proc-batch-loop-safe.sql from SQL Rig for SQL Server (SQL Server 2016 SP1 and later, Azure SQL Managed Instance).

CREATE OR ALTER PROCEDURE dbo.usp_PurgeEventLog
    @OlderThanDays int,
    @BatchSize     int     = 2000,
    @MaxRows       bigint  = 1000000,
    @DelayBetween  char(8) = '00:00:01',
    @DryRun        bit     = 1
AS
BEGIN
    SET NOCOUNT ON; SET XACT_ABORT ON;
    IF @OlderThanDays < 1 OR @BatchSize NOT BETWEEN 1 AND 4999
        THROW 50010, '@OlderThanDays must be >= 1 and @BatchSize between 1 and 4999.', 1;

    DECLARE @Cutoff datetime2(0) = DATEADD(DAY, -@OlderThanDays, SYSDATETIME());
    DECLARE @Total bigint = 0, @Rows int = 1, @Batch int = 0;

    IF @DryRun = 1
    BEGIN
        SELECT @Total = COUNT_BIG(*) FROM dbo.EventLog WHERE EventAt < @Cutoff;
        PRINT CONCAT('DRY RUN: ', @Total, ' rows match (cap ', @MaxRows,
                     ', batch ', @BatchSize, '). Nothing deleted.');
        RETURN 0;
    END;

    WHILE @Rows > 0 AND @Total < @MaxRows
    BEGIN
        BEGIN TRY
            BEGIN TRANSACTION;
            DELETE TOP (@BatchSize) FROM dbo.EventLog WHERE EventAt < @Cutoff;
            SET @Rows = @@ROWCOUNT;
            SET @Total += @Rows; SET @Batch += 1;
            INSERT dbo.BatchRunLog (ProcName, BatchNo, RowsAffected, TotalRows)
            VALUES (OBJECT_NAME(@@PROCID), @Batch, @Rows, @Total);
            COMMIT TRANSACTION;
        END TRY
        BEGIN CATCH
            IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
            THROW;
        END CATCH;

        RAISERROR('Batch %d: %d rows', 0, 1, @Batch, @Rows) WITH NOWAIT;
        IF @Rows > 0 WAITFOR DELAY @DelayBetween;
    END;
    PRINT CONCAT('Done. Total rows deleted: ', @Total);
    RETURN 0;
END;

The log table is simple:

CREATE TABLE dbo.BatchRunLog (
    Id           bigint IDENTITY PRIMARY KEY,
    ProcName     sysname   NOT NULL,
    BatchNo      int       NOT NULL,
    RowsAffected int       NOT NULL,
    TotalRows    bigint    NOT NULL,
    LoggedAt     datetime2(3) NOT NULL DEFAULT SYSDATETIME());

Running it

-- 1. See what would happen
EXEC dbo.usp_PurgeEventLog @OlderThanDays = 90;              -- @DryRun defaults to 1

-- 2. After review, a capped real run
EXEC dbo.usp_PurgeEventLog @OlderThanDays = 90, @MaxRows = 50000, @DryRun = 0;

-- 3. Watch progress from another session
SELECT TOP (20) * FROM dbo.BatchRunLog ORDER BY Id DESC;

Start with a small @MaxRows and check timing and blocking before you raise it.

Details that matter

Index the predicate. Without an index on EventAt, every batch scans the table to find the next 2,000 rows, and later batches get slower as they scan past rows already deleted. Check the plan of the DELETE for a seek.

Transaction log. Batching keeps each transaction small, but the total log volume is the same. In FULL recovery, log space is only reused after a log backup, so make sure log backups run during long purges, or the log will grow. In SIMPLE recovery, checkpoints clear it between batches.

Do not wrap the loop in an outer transaction. That turns all the batches back into one long transaction and removes the benefit. If you need all-or-nothing behavior, a batched delete is the wrong tool; consider partition switching instead.

Foreign keys and triggers. Cascading deletes and AFTER DELETE triggers run per batch and can make each batch much more expensive. Test on a copy with production-like volume.

Keep the predicate deterministic. Compute @Cutoff once before the loop, as above. Calling SYSDATETIME() inside the loop moves the boundary while the run is in progress.

Rollback. Deleted rows are gone once each batch commits. Take a backup, or copy the rows to an archive table with OUTPUT deleted.* INTO, before any production purge.

Why not TRUNCATE

TRUNCATE TABLE has no WHERE clause, resets identity values, and is blocked by foreign keys. It is the right tool for emptying a staging table, not for removing old rows from a live one.