Deleting Millions of Rows in SQL Server, One Batch at a Time

Matt Pavey

I run scheduled jobs on several systems that remove data past its retention window. Depending on the system, that might mean anything older than ninety days or a year. On a busy logging or event table, one cleanup can involve millions of rows.

The SQL is simple:

DELETE FROM dbo.EventLog
WHERE CreatedUtc < @Cutoff;

The amount of work behind it is not. SQL Server has to find the rows, delete them, and maintain the affected indexes. Triggers and foreign-key relationships can add more work. A large delete can block other requests and put substantial pressure on the transaction log. Canceling it can also leave SQL Server with a lot to roll back.

For these jobs, I prefer smaller transactions. Each batch commits a limited amount of work, and the job can pause between batches. That gives me something I can tune while watching how the application responds.

First, the job needs an efficient way to find expired rows. An index beginning with CreatedUtc is worth evaluating if an equivalent index does not already exist:

CREATE NONCLUSTERED INDEX IX_EventLog_CreatedUtc
    ON dbo.EventLog (CreatedUtc);

Without a useful access path, each batch may spend more time finding its next rows than deleting them. The index can help with that search, though SQL Server also has to maintain it during deletion. Check the actual plan; a batch size does not fix an expensive scan.

Here is a starting pattern for a dedicated cleanup connection. This example assumes CreatedUtc is a datetime2 column containing UTC values:

SET NOCOUNT ON;

IF @@TRANCOUNT <> 0
BEGIN
    THROW 50000, 'Run this cleanup without an existing transaction.', 1;
END;

SET IMPLICIT_TRANSACTIONS OFF;
SET XACT_ABORT ON;

DECLARE @BatchSize INT = 1000;
DECLARE @Cutoff DATETIME2(7) = DATEADD(DAY, -90, SYSUTCDATETIME());
DECLARE @Rows INT;

WHILE 1 = 1
BEGIN
    DELETE TOP (@BatchSize)
    FROM dbo.EventLog
    WHERE CreatedUtc < @Cutoff;

    SET @Rows = @@ROWCOUNT;

    IF @Rows = 0
        BREAK;

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

The cutoff is calculated once, so the retention boundary stays fixed during the run. The loop captures @@ROWCOUNT immediately after each delete and stops when a batch removes no rows. DELETE TOP does not promise an order; for this cleanup, any rows older than the cutoff are eligible.

The transaction setup matters. With no existing transaction and implicit transactions disabled, each DELETE commits independently. Do not wrap the loop in an outer transaction. That would keep the work uncommitted across batches, including during the pauses. If a later batch fails, the earlier committed batches remain done.

A thousand rows and a one-second pause are starting values to test. Smaller batches usually shorten each transaction; a pause reduces how aggressively the cleanup competes for resources. Neither setting guarantees that another request will avoid waiting.

The often-quoted 5,000-lock escalation threshold is about locks, not deleted rows. Index access and related operations can acquire additional locks, and lock memory pressure also matters. Deleting 4,500 rows is not a reliable way to stay below the threshold. Blocking can happen without escalation, too.

The transaction log needs separate attention. DELETE is fully logged, including when it runs in small batches. Batching limits the size of each transaction; it does not make the deletions minimally logged.

Whether inactive log space becomes reusable depends on the recovery model and log truncation:

  • Under SIMPLE recovery, a checkpoint can make inactive log space reusable. A commit does not automatically cause a checkpoint after every batch.
  • Under FULL or BULK_LOGGED recovery, regular log backups are normally required for reuse. Committing batches does not replace those backups.

Other activity can delay reuse, including an active transaction or an availability replica that has fallen behind. When the log keeps growing, check the reported reason:

SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases
WHERE database_id = DB_ID();

Reusable space stays inside the log file; truncation does not shrink the physical file. I plan log capacity and backup frequency around the cleanup volume, rather than expecting batching to keep the file at a fixed size.

Before leaving a job unattended, I would measure batch duration, blocking, log use, and application response times under realistic load. I would also give it a runtime limit so a growing backlog cannot push an overnight cleanup into the busiest part of the day.

The pause may make the job take longer overall, but elapsed time alone does not tell me whether it is behaving well. I want the expired rows removed at a pace the system can support while people continue using it.