Delete Old SQL Server Data in Batches That Can Restart

A retention job that deletes several years of rows in one transaction can monopolize locks, consume transaction log space, and take a long time to roll back. Splitting the work into batches helps, but a loop around DELETE TOP is only part of the solution. A reliable job also needs a stable definition of eligible data, an efficient access path, and an honest definition of completion.

Before deleting anything, translate the policy into a predicate. Is retention based on event time, ingestion time, or the date a case was closed? Are some records under a hold? If dependent records or an archive must survive, those requirements belong in the design before the first production run.

Keep the cutoff fixed

The following example deletes only temporary practice data. Three rows precede the cutoff. With a batch size of two, the delete counts are 2, 1, then 0; rows 4 and 5 remain.

CREATE TABLE #Events (
    EventId bigint NOT NULL PRIMARY KEY,
    OccurredAtUtc datetime2(0) NOT NULL
);
CREATE INDEX IX_Events_Retention ON #Events(OccurredAtUtc, EventId);
INSERT #Events VALUES
(1, '20220901'), (2, '20220902'), (3, '20220903'),
(4, '20221001'), (5, '20221002');

DECLARE @Cutoff datetime2(0) = '20221001';
DECLARE @BatchSize int = 2, @Rows int = 1, @Batches int = 0;
SET XACT_ABORT ON;
IF @@TRANCOUNT <> 0
    THROW 50001, 'Run this example without an existing transaction.', 1;

WHILE @Rows > 0 AND @Batches < 10
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;
        ;WITH Victims AS (
            SELECT TOP (@BatchSize) EventId, OccurredAtUtc
            FROM #Events
            WHERE OccurredAtUtc < @Cutoff
            ORDER BY OccurredAtUtc, EventId
        )
        DELETE FROM Victims;
        SET @Rows = @@ROWCOUNT;
        COMMIT TRANSACTION;
        SET @Batches += 1;
        SELECT @Batches AS BatchNumber, @Rows AS DeletedRows;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
        THROW;
    END CATCH;
END;
SELECT EventId, OccurredAtUtc FROM #Events ORDER BY EventId;
DROP TABLE #Events;

The cutoff is calculated once and stays fixed throughout the run. A production job can derive it from the agreed policy and record it in a run log. Recomputing a moving cutoff in every iteration makes the target change while the job is working and complicates reconciliation.

DELETE TOP alone does not promise which eligible rows it chooses. The ordered CTE makes the selection deliberate, using EventId to break timestamp ties. An index starting with the retention timestamp and then the identifier can avoid repeatedly scanning unrelated newer rows. Additional eligibility predicates may require a different index or cause residual filtering.

The sample refuses an existing transaction because an outer transaction would prevent each batch commit from actually releasing the overall transaction. Do not paste the loop inside a job framework that silently wraps the entire run in one transaction.

Bound the impact, not just the row count

Each batch commits separately. Capture @@ROWCOUNT immediately after DELETE, before another statement changes it. The maximum batch count provides a stopping point even when new eligible rows keep arriving. In a real scheduler, use a time budget as well and distinguish "budget exhausted" from "nothing left".

A fixed row count does not imply fixed cost. Wide rows, nonclustered indexes, cascading foreign keys, and triggers can multiply log generation and locking. Tune batch size using observed duration, blocking, log consumption, and replica lag. A pause belongs after a commit, not while holding the batch's locks.

Small batches do not guarantee that lock escalation cannot happen. Do not begin by forcing ROWLOCK everywhere. Diagnose the actual access path and workload first. Ordinary deletes remain logged; under the full recovery model, committing batches does not replace the log backups needed for log reuse.

If an archive is required, establish a durable handoff before deletion. A successful network send is not proof that the archive committed. Use a transactional local staging step or an idempotent external protocol with acknowledgements and reconciliation.

Make retries and completion verifiable

The predicate-based loop naturally revisits remaining eligible rows after an interruption. Record the cutoff, rows deleted, elapsed time, and final status for each run. If a connection drops during commit, the caller may not know whether that batch committed; rerunning the eligibility query is safer than assuming failure.

Be careful with a permanently increasing checkpoint. A late-arriving row with an old event timestamp can fall behind it and never be selected. Either schedule fresh sweeps of the entire eligible range or define an ingestion-based policy that makes the checkpoint valid.

READPAST is not a general completion shortcut. Skipped locked rows can leave eligible data behind, so an empty skipped scan does not prove the job finished. Validate remaining eligible rows without that assumption and report any deferred work.

For very large time-based datasets, partition retirement may be more appropriate, but it requires compatible table and index design and separate validation. Whatever technique is used, success means the agreed data was removed and retained data stayed intact, not merely that the job exited without an error.

Technical references: Microsoft Learn: DELETE · Microsoft Learn: Lock escalation.