Large UPDATE and DELETE operations in SQL Server can consume significant transaction log space, hold locks for a long time, and make failures expensive to recover from. Transaction batching solves this by dividing a large data modification into smaller transactions that can be committed independently.

This guide explains why batching is useful, how to implement it with T-SQL, and which precautions to take in production workloads such as data warehouses, staging tables, and archival jobs.

What Is Transaction Batching in SQL Server?

Transaction batching means processing a large number of rows in smaller groups. Each group runs inside its own transaction and is committed before the next group begins.

For example, instead of updating one million rows in a single transaction, you can update 2,000 rows at a time. This approach can reduce the duration of locks, limit the amount of work that must be rolled back, and make transaction log usage easier to manage.

Why Use Transaction Batching?

  • Lower transaction log pressure: smaller transactions can make log management more predictable.
  • Shorter lock duration: locks are released after each batch commits.
  • Smaller rollbacks: a failed batch does not automatically require the entire operation to be rolled back.
  • Better concurrency: shorter transactions can reduce blocking for other sessions.
  • Operational control: batch size, delay, and retry behavior can be adjusted for the workload.

Example Setup

The following examples use a temporary table containing six rows. The same pattern can be applied to permanent tables, provided that the batch query has a reliable filter or ordering strategy.

CREATE TABLE #temp (
    id INT IDENTITY(1,1),
    name VARCHAR(10),
    flag INT NULL
);

INSERT INTO #temp (name)
VALUES ('abc'), ('abc'), ('abc'),
       ('xyz'), ('xyz'), ('xyz');

SELECT *
FROM #temp;

Batching Updates

In this example, two rows are updated per transaction. The loop stops when no qualifying rows remain.

<pre class="wp-block-syntaxhighlighter-code">DECLARE @BatchSize INT = 2;
DECLARE @RowsAffected INT = 1;

WHILE @RowsAffected > 0
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;

        ;WITH BatchRows AS
        (
            SELECT TOP (@BatchSize) id
            FROM #temp
            WHERE name = 'abc'
              AND flag IS NULL
            ORDER BY id
        )
        UPDATE t
        SET flag = 1
        FROM #temp AS t
        INNER JOIN BatchRows AS b
            ON b.id = t.id;

        SET @RowsAffected = @@ROWCOUNT;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0
            ROLLBACK TRANSACTION;

        THROW;
    END CATCH;
END;

SELECT *
FROM #temp;</pre>
Transaction batching in SQL Server for update.

Batching Deletes

The delete pattern is similar. The query selects a limited number of rows, deletes them, commits the transaction, and repeats until no rows remain.

<pre class="wp-block-syntaxhighlighter-code">DECLARE @BatchSize INT = 2;
DECLARE @RowsAffected INT = 1;

WHILE @RowsAffected > 0
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;

        ;WITH BatchRows AS
        (
            SELECT TOP (@BatchSize) id
            FROM #temp
            WHERE flag = 1
            ORDER BY id
        )
        DELETE t
        FROM #temp AS t
        INNER JOIN BatchRows AS b
            ON b.id = t.id;

        SET @RowsAffected = @@ROWCOUNT;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0
            ROLLBACK TRANSACTION;

        THROW;
    END CATCH;
END;

SELECT *
FROM #temp;</pre>
Transaction batching in SQL Server for delete

SET ROWCOUNT vs. TOP (@BatchSize)

The original example used SET ROWCOUNT to limit the number of affected rows. Although it can work, using TOP (@BatchSize) in a targeted CTE is generally clearer because the row limit is visible directly in the statement.

For repeatable processing, use a stable key such as an identity column, primary key, or another indexed column in the ORDER BY. Always make sure the filter changes after each successful batch; otherwise, the loop can continue indefinitely.

Transaction Batching Best Practices

  • Keep transactions short. Do not perform user interaction, long-running queries, or unnecessary processing inside the transaction.
  • Choose a practical batch size. Start with a modest value such as 500, 1,000, or 2,000 rows and measure the effect on log usage, blocking, and throughput.
  • Use an indexed filter. A suitable index can prevent every batch from scanning the entire table.
  • Use deterministic ordering. Process rows by a stable key to reduce the chance of repeatedly scanning the same data.
  • Handle errors explicitly. Use TRY...CATCH, check XACT_STATE(), and roll back an active transaction before rethrowing the error.
  • Monitor the transaction log. Batching does not remove the need for sufficient log space or an appropriate recovery strategy.
  • Be careful with foreign keys, triggers, and indexes. These can make each batch more expensive than expected.
  • Control concurrency. Running many batches in parallel against the same table may increase blocking and reduce performance.
  • Make the batch size configurable. Different environments and table sizes may require different settings.

Important Note About CHECKPOINT

A CHECKPOINT should not be treated as a command that simply frees all transaction log space. Log truncation depends on the recovery model and whether there are active transactions, replication, availability features, backups, or other log-reuse blockers.

Use transaction-log monitoring to identify the actual reason for log growth or delayed truncation. Committing batches helps complete transactions, but it does not guarantee immediate log-file shrinkage.

When Should You Use Batching?

  • Deleting old audit or staging data.
  • Updating large data warehouse tables.
  • Archiving records in controlled increments.
  • Backfilling or correcting historical data.
  • Processing large queues or migration tables.
  • Performing maintenance during business hours when blocking must be limited.

Conclusion

Transaction batching is a practical technique for executing large SQL Server data modifications with better control over transaction duration, locking, rollback scope, and operational risk. Use a stable key, a sensible batch size, explicit error handling, and transaction-log monitoring. Test the workload under realistic concurrency before applying the pattern to production.

Pro tip: If batching causes blocking or deadlocks, review indexes, transaction duration, isolation level, and concurrent access patterns. Learn how to find and manage deadlocks in SQL Server.

See more

Download Now
Kunal Rathi

With over 15 years of experience in data engineering and analytics, I've assisted countless clients in gaining valuable insights from their data. As a dedicated supporter of Data, Cloud and DevOps, I'm excited to connect with individuals who share my passion for this field. If my work resonates with you, we can talk and collaborate.