Large cleanup jobs can put pressure on a busy database when too much data is deleted in one transaction. The delete may be valid, but the transaction can hold locks for longer, grow the transaction log quickly, and make normal reads or writes wait behind the cleanup. Batch deletes break that job into smaller repeatable passes. Each pass removes up to the configured number of old rows, records the affected row count, commits, then continues until no matching rows remain. SQL Server batch delete code should rely on DELETE TOP for row limits, while SET ROWCOUNT should be left out of DELETE, INSERT, and UPDATE logic.
Batch Delete Mechanics
Deleting rows in batches turns a large retention cleanup into a repeated series of smaller transactions. The retention rule stays the same, such as removing rows older than a cutoff date, while the database gains commit points between passes instead of carrying the entire deletion inside a single transaction.
SQL Server still has to remove rows, update related indexes, and record those changes in the transaction log. Batching limits the amount of activity handled during each pass, which lets the cleanup move through older data without keeping one long transaction open for the full run.
Small Transactions
Most of the pressure from a large delete comes from the transaction surrounding it. SQL Server records row changes, updates affected indexes, and protects rows while the statement remains active. When a transaction covers a huge row set, locks can remain active longer and the transaction log can grow quickly before the commit finishes.
Breaking the deletion into smaller passes changes that transaction boundary. We select a fixed number of rows, delete that group, commit the pass, and continue with the next group. Every commit gives the cleanup a stopping point, so cancellation or failure does not leave the entire retention run inside one open transaction.
Batch size should reflect the table, its indexes, and the traffic around it rather than being copied from an unrelated cleanup job. Smaller values are usually a safer starting point on a busy table. Tables with narrow rows and few indexes can usually handle more rows per pass than tables with wide rows, triggers, foreign keys, or several indexes. After a test run, we can raise or lower the batch size based on pass duration, blocking, and transaction log growth.
We can write the transaction boundary like this:
DECLARE @BatchSize int = 5000;
BEGIN TRANSACTION;
DELETE TOP (@BatchSize)
FROM dbo.EventLog
WHERE CreatedAt < DATEADD(day, -90, SYSUTCDATETIME());
COMMIT TRANSACTION;During this pass, SQL Server removes no more than 5,000 rows whose CreatedAt value falls before the retention cutoff, then commits the transaction. TOP caps the row count, while the WHERE clause restricts the deletion to rows covered by the retention rule. Without that predicate, SQL Server would still limit the row count, but it could delete any rows selected by the execution plan rather than rows that have expired.
Recalculating the cutoff in several places can produce slightly different boundaries during a longer cleanup. We can calculate it before the delete and reuse the same variable throughout the batch run:
DECLARE @BatchSize int = 5000;
DECLARE @Cutoff datetime2(0) = DATEADD(day, -90, SYSUTCDATETIME());
BEGIN TRANSACTION;
DELETE TOP (@BatchSize)
FROM dbo.EventLog
WHERE CreatedAt < @Cutoff;
COMMIT TRANSACTION;Keeping the cutoff in a variable holds the same retention boundary for the entire pass. If the cleanup later moves into a loop, we calculate @Cutoff before the loop and reuse that value for every batch so the deletion boundary does not move while the run remains active.
Cutoff Boundaries
Retention rules establish which rows are old enough for deletion, so the cutoff column should match the event that begins the retention period. CreatedAt fits rows that expire according to insertion time, while CompletedAt, ArchivedAt, or ExpiresAt can fit records tied to process state or an expiration policy.
The delete predicate should match the retention rule without reaching beyond it. If event log rows expire after 90 days, the predicate should compare the approved date column with that cutoff. When deletion applies only to completed rows, the status condition belongs in the same predicate. Loose conditions can match more data than expected as table contents change after testing.
Before deleting anything, we can review a small sample with the same cutoff rule:
DECLARE @Cutoff datetime2(0) = DATEADD(day, -90, SYSUTCDATETIME());
SELECT TOP (25)
EventLogId,
CreatedAt,
EventType
FROM dbo.EventLog
WHERE CreatedAt < @Cutoff
ORDER BY CreatedAt, EventLogId;Running this query returns the oldest 25 rows that meet the retention boundary without changing any data. ORDER BY CreatedAt, EventLogId arranges the preview by age, while EventLogId breaks ties for rows that share the same timestamp. Keeping the preview predicate identical to the delete predicate also helps confirm that both statements target the same row set.
Rows containing NULL in the cutoff column need an explicit policy decision because CreatedAt < @Cutoff does not match them. Those records remain in the table, which can fit a policy where missing dates belong to a separate repair process instead of the retention delete.
When missing dates belong in the same cleanup, the predicate must state that rule directly:
DECLARE @Cutoff datetime2(0) = DATEADD(day, -90, SYSUTCDATETIME());
SELECT TOP (25)
EventLogId,
CreatedAt,
EventType
FROM dbo.EventLog
WHERE CreatedAt IS NULL
OR CreatedAt < @Cutoff
ORDER BY CreatedAt, EventLogId;With this predicate, rows lacking a CreatedAt value become deletion candidates alongside rows older than the cutoff. SQL Server sorts NULL values before non-NULL values in ascending order, so they appear first in the preview. That branch belongs only where the approved retention policy calls for removing those records.
Ordered Row Picking
Although DELETE TOP limits the number of rows in a pass, it does not guarantee oldest-first deletion by itself. When the cleanup should move through the table by age, row selection needs to happen before the delete identifies its targets.
T-SQL commonly handles this by selecting the next group of row identifiers in the required order, then deleting from the target table through a join back to those identifiers. SQL Server receives an exact set of IDs for the pass, while the ordering remains inside the row-selection query.
We can write the ordered delete with a common table expression:
DECLARE @BatchSize int = 5000;
DECLARE @Cutoff datetime2(0) = DATEADD(day, -90, SYSUTCDATETIME());
;WITH NextRows AS
(
SELECT TOP (@BatchSize)
EventLogId
FROM dbo.EventLog
WHERE CreatedAt < @Cutoff
ORDER BY CreatedAt, EventLogId
)
DELETE e
FROM dbo.EventLog AS e
INNER JOIN NextRows AS n
ON n.EventLogId = e.EventLogId;Inside NextRows, SQL Server selects the oldest matching EventLogId values up to the batch limit. The outer DELETE then removes only rows whose IDs appear in that result. CreatedAt controls the age order, while EventLogId breaks ties so rows sharing the same timestamp still receive a stable order.
Large tables can benefit from an index that supports both the cutoff predicate and the requested order. When the retention query always filters by CreatedAt and orders by CreatedAt, EventLogId, indexing those columns gives SQL Server a more direct route to the next batch:
CREATE INDEX IX_EventLog_CreatedAt_EventLogId
ON dbo.EventLog (CreatedAt, EventLogId);This index supports WHERE CreatedAt < @Cutoff along with ORDER BY CreatedAt, EventLogId. It does not remove the cost of deleting rows or updating other indexes, but it can reduce the table pages SQL Server scans while locating the next batch. Before adding it, we should check for an existing index with the same leading columns because a duplicate would add storage and write cost without helping the deletion.
Loop Flow
Every batch loop needs a stopping condition, and the delete count provides that condition. We initialize the count with a positive value so the first pass runs, then capture @@ROWCOUNT immediately after the delete. When a pass removes zero rows, no records remain for that cutoff and predicate, so the loop ends.
Wrapping every pass in its own transaction lets each batch commit independently:
DECLARE @BatchSize int = 5000;
DECLARE @RowsDeleted int = 1;
DECLARE @Cutoff datetime2(0) = DATEADD(day, -90, SYSUTCDATETIME());
WHILE @RowsDeleted > 0
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
;WITH NextRows AS
(
SELECT TOP (@BatchSize)
EventLogId
FROM dbo.EventLog
WHERE CreatedAt < @Cutoff
ORDER BY CreatedAt, EventLogId
)
DELETE e
FROM dbo.EventLog AS e
INNER JOIN NextRows AS n
ON n.EventLogId = e.EventLogId;
SET @RowsDeleted = @@ROWCOUNT;
COMMIT TRANSACTION;
IF @RowsDeleted > 0
WAITFOR DELAY '00:00:00.200';
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
ROLLBACK TRANSACTION;
THROW;
END CATCH;
END;The loop enters its first pass because @RowsDeleted starts at 1. Immediately after the delete, @@ROWCOUNT is copied into the variable because a later statement can replace its value. Capturing it before COMMIT TRANSACTION keeps the stopping condition tied to the delete that just finished.
After the row count has been saved, COMMIT TRANSACTION closes the transaction before the pause begins. The short WAITFOR DELAY runs only when a batch deleted rows, giving competing activity a brief opening between passes without holding the transaction open during the delay. When the next pass finds no matching rows, @RowsDeleted becomes zero and the loop exits without pausing again.
If the delete fails before the commit, the CATCH block checks XACT_STATE() to find out if a transaction remains active. ROLLBACK TRANSACTION reverses the failed pass, and THROW returns the original error to the caller so the loop cannot continue after that failure.
Cleanup Job Control
Long retention runs need a dependable stopping rule, a time boundary, and enough run data to explain what happened after the session ends. The affected-row count controls normal completion, while totals and elapsed time let an operator pause the run without undoing committed batches. Those checks belong after the current transaction commits, so status recording or scheduler logic does not keep deletion locks active longer than needed. Normal completion means the latest pass found no eligible rows. Reaching a run limit means eligible rows can remain for the next scheduled run, while an error means the current pass should roll back and report the failure. Keeping those outcomes separate prevents a time-limited stop from being reported as a fully finished retention run.
Row Count Checks
Reliable loop termination comes from reading @@ROWCOUNT immediately after the DELETE. SQL Server sets it to the number of rows affected by the latest data modification statement. Later statements can replace or reset that value. COMMIT TRANSACTION, PRINT, SET, and several other statements change it, so the assignment to @RowsDeleted must come before them. SET NOCOUNT ON suppresses affected-row messages sent to the client, but @@ROWCOUNT still receives the affected-row value.
Batch reporting can stay in variables when only the final result is needed. We can place these counters before the loop:
DECLARE @RowsDeleted int = 1;
DECLARE @BatchNumber int = 0;
DECLARE @TotalRowsDeleted bigint = 0;@RowsDeleted starts above zero so the loop enters its first pass. @BatchNumber tracks committed batches, while @TotalRowsDeleted records the full number of rows removed during the run. bigint fits the total because a long retention job can remove more rows than a single batch counter could reasonably hold.
The delete count must be saved before any commit or status statement changes @@ROWCOUNT:
SET @RowsDeleted = @@ROWCOUNT;
COMMIT TRANSACTION;
SET @BatchNumber += 1;
SET @TotalRowsDeleted += @RowsDeleted;
SELECT
@BatchNumber AS batch_number,
@RowsDeleted AS rows_deleted,
@TotalRowsDeleted AS total_rows_deleted,
SYSUTCDATETIME() AS batch_finished_at;The first assignment preserves the delete result before the commit resets @@ROWCOUNT. Reporting happens after the commit, so returning status to the caller does not extend the transaction. We then increase the batch counter, add the current delete count to the running total, and return enough information to track progress during the session.
Persistent job history can store the same values in a regular table after every commit. That record can include the run identifier, batch number, deleted-row count, start time, finish time, and error state. Keeping the insert outside the deletion transaction means a logging delay does not hold locks on dbo.EventLog. Some environments may prefer the batch log and delete to commit in the same transaction, but that choice lengthens the transaction and should be made deliberately.
Stopping After No Matches
Zero affected rows provide the final test for normal completion. Deleting fewer rows than @BatchSize probably means the pass reached the end of the eligible range, but running a final pass gives the loop an exact answer. This remains useful when concurrent activity changes rows near the cutoff or when predicates include status values that can change during the retention run.
Time-bounded jobs need a separate exit reason. The elapsed-time check should run only after the delete count has been captured and the transaction has committed:
DECLARE @RunStartedAt datetime2(3) = SYSUTCDATETIME();
DECLARE @RunLimitMinutes int = 10;
DECLARE @ExitReason varchar(20) = NULL;
IF @RowsDeleted = 0
BEGIN
SET @ExitReason = 'Completed';
BREAK;
END
ELSE IF SYSUTCDATETIME() >= DATEADD(minute, @RunLimitMinutes, @RunStartedAt)
BEGIN
SET @ExitReason = 'TimeLimit';
BREAK;
END;@RunStartedAt records the start of the cleanup, while @RunLimitMinutes defines how long the session may continue. After every committed batch, we first check for zero deleted rows. If rows were removed, we then compare the current UTC time with the allowed end time. The order keeps normal completion separate from a stop caused by the time limit.
Reaching TimeLimit leaves prior batches committed and lets the next scheduled execution continue from the same cutoff rule. The job result should report that eligible rows can remain instead of marking the cleanup complete. Batch number and total deleted rows also help distinguish healthy partial progress from a run that repeatedly stops before deleting anything.
Locking Pressure
Deletes still compete with other activity for access to table and index resources. Smaller transactions reduce the number of locks held at the same time and release transaction locks at every commit, but batching does not remove blocking. Two sessions that try to change the same eligible rows can still wait on each other, and a delete can block writes that touch related index entries.
Batch size should be judged by transaction duration and observed blocking rather than row count alone. Each deleted row can require changes in the base table and several indexes, so deleted rows do not equal acquired locks. Wider rows, extra indexes, referential checks, triggers, and scans can raise the amount of database activity within a pass.
Pauses between batches can give competing sessions room to continue, but the delay belongs after COMMIT TRANSACTION. Placing WAITFOR DELAY before the commit keeps locks active during the pause and defeats much of the benefit gained from smaller transactions.
Lock Escalation
Fine-grained locks can be converted to a table lock when SQL Server decides escalation is warranted. SQL Server checks lock memory and the number of locks acquired on a single table reference. Keeping a batch below 5,000 deleted rows does not guarantee that escalation will be avoided because the threshold concerns acquired locks rather than deleted rows, and memory pressure can also trigger escalation.
Keeping transactions short and reducing the rows examined by the delete lowers the chance of an unwanted escalation. The ROWLOCK hint requests row locks for the statement, but SQL Server can still escalate later. Hints should not replace testing, monitoring, or an index that supports the retention predicate. Blocking by itself does not prove that lock escalation occurred. Ordinary row, page, or index conflicts can produce the same outward symptom. Extended Events with the lock_escalation event can confirm an escalation, while current request and lock views help identify the waiting session and the resource involved.
Watching Active Locks
Live monitoring is most useful while the cleanup is running. From a separate connection, sys.dm_exec_requests can report the current blocker and wait data for the cleanup session, while sys.dm_tran_locks reports granted and waiting lock requests. Both views represent current state rather than historical records.
We can inspect a known cleanup session with the following queries:
DECLARE @CleanupSessionId smallint = 57;
SELECT
session_id,
status,
command,
blocking_session_id,
wait_type,
wait_time,
wait_resource,
total_elapsed_time
FROM sys.dm_exec_requests
WHERE session_id = @CleanupSessionId;
SELECT
resource_type,
request_mode,
request_status,
COUNT_BIG(*) AS lock_request_count
FROM sys.dm_tran_locks
WHERE request_session_id = @CleanupSessionId
GROUP BY
resource_type,
request_mode,
request_status
ORDER BY
lock_request_count DESC;The first query reports the current state of the cleanup request. When blocking_session_id is greater than zero, it identifies the session blocking the request if SQL Server can determine that relationship, while wait_type and wait_resource provide more detail about the wait. The second query groups lock requests by resource type, mode, and status so we can review granted locks and waiting requests without reading every row separately.
Repeated samples give a better account than an isolated reading because locks can appear and disappear between batches. Persistent blocking, rising pass duration, or frequent escalation usually calls for a smaller batch, a longer pause between passes, or an index review focused on the retention predicate.
Conclusion
Batch deletion comes down to how each pass is controlled. The cutoff stays fixed, the next rows are chosen in a defined order, and every batch closes its transaction before the loop pauses or continues. Capturing @@ROWCOUNT directly after the delete gives the loop a reliable stopping point, while the transaction and error handling keep failures limited to the current pass. Run limits and progress totals help control longer sessions, while short transactions and lock monitoring keep database pressure observable between batches. With those mechanics in place, SQL Server can move through expired rows in measured batches rather than carrying the full cleanup inside one long transaction.
