How to Delete or Update Millions of Rows in SQL Server Safely (Batching)

October 26, 2026

Delete or update millions of rows in SQL Server without blocking users or filling the log: batching scripts, lock escalation and log tips. Sooner or later you need to delete five years of audit logs or update a column on 30 million rows. Run it as one statement and you can lock the table for an hour, fill the transaction log, block every user and, if you cancel, wait just as long again for the rollback. The fix is simple: do the work in small batches.

How to batch large deletes and updates in SQL Server

Why one big statement hurts

One huge transaction: every row change is logged and kept until the statement commits, so the transaction log grows and cannot be reused until it finishes.
Lock escalation: once a single statement holds roughly 5,000 locks on a table, SQL Server tries to replace them with one table lock. Then nobody else can write to that table.
Painful rollback: if you cancel after 40 minutes, SQL Server has to undo everything, which can take just as long.
Replicas fall behind: Availability Group secondaries and replication must replay the whole change.

Step 1: make sure the filter is indexed

Each batch has to find its next set of rows quickly. If the WHERE clause has no supporting index, every batch scans the table and the whole job gets slower as it goes. For example, deleting old audit rows by date needs an index on the date column:

CREATE NONCLUSTERED INDEX IX_AuditLog_CreatedAt
ON dbo.AuditLog (CreatedAt);

Step 2: delete in batches

DECLARE @Cutoff    DATETIME2 = '2021-01-01',
        @BatchSize INT       = 4000,
        @Rows      INT       = 1;

WHILE @Rows > 0
BEGIN
    DELETE TOP (@BatchSize)
    FROM dbo.AuditLog
    WHERE CreatedAt < @Cutoff;

    SET @Rows = @@ROWCOUNT;

    WAITFOR DELAY '00:00:00.200';   -- give other sessions room to work
END;

Each DELETE is its own short transaction, so locks are released quickly and the log space can be reused.
A batch size below about 5,000 rows usually avoids lock escalation. Start there, then tune: bigger batches finish faster, smaller ones block less.
Do not wrap the loop in BEGIN TRAN ... COMMIT; that turns it back into one giant transaction.
The job can be stopped and restarted safely; it simply picks up the rows that are left.

Step 3: update in batches by walking the key

Updates are trickier because the updated rows may still match the filter. The cleanest approach is to walk through the clustered key in ranges:

DECLARE @BatchSize BIGINT = 5000,
        @StartId   BIGINT = 0,
        @MaxId     BIGINT;

SELECT @MaxId = MAX(PayrollLineId) FROM dbo.PayrollLine;

WHILE @StartId < @MaxId
BEGIN
    UPDATE dbo.PayrollLine
    SET    PayCode = 'BASIC'
    WHERE  PayrollLineId > @StartId
      AND  PayrollLineId <= @StartId + @BatchSize
      AND  PayCode = 'BAS';

    SET @StartId += @BatchSize;
END;

Each batch uses a fast range seek on the clustered key, and progress is easy to track. If you need to resume after stopping, record @StartId in a small control table.

Step 4: keep the transaction log under control

In the FULL recovery model, log space is only reused after a log backup. Make sure log backups keep running during the job, or schedule extra ones.
In the SIMPLE recovery model, space is reused after each checkpoint, so short batches keep the log small on their own.
Watch free log space while the job runs, for example with sys.dm_db_log_space_usage.

Step 5: run it gently

Run large jobs outside business hours where possible.
Watch for blocking while it runs, and lower the batch size or add a longer delay if users are affected.
Print or log progress so you know how far it has got.

Faster alternatives when they fit

TRUNCATE TABLE removes every row almost instantly, but cannot filter and has restrictions such as foreign keys.
Copy and swap: when you are deleting most of a table, it can be faster to copy the rows you keep into a new table, then swap names in a short maintenance window.
Partition switching: on a partitioned table, you can switch out or truncate an entire old partition almost instantly. This is the best long-term design for tables that grow by date.

Quick checklist

Index the filter column first.
Use batches of a few thousand rows, each in its own transaction.
Walk the clustered key for large updates.
Keep log backups running and watch log space.
Monitor blocking and adjust batch size and delay.

Related reading:

Search This Blog

SQL & Cloud Lab