A retention delete should leave the application usable while it makes progress. When deleting millions of rows, the transaction boundaries and access path matter as much as the row count.
Why Deleting Millions of Rows in One Statement Causes Trouble
A DELETE logs row changes and maintains the affected indexes. Foreign-key checks, cascading actions, and triggers can add work beyond the rows named by the original statement. A single transaction retains its recovery requirements until it ends, so the log must accommodate that work alongside concurrent activity. A generous maintenance window does not create extra disk space.
Long transactions also hold locks and can interfere with ordinary requests. Canceling the operation starts another problem: rollback must undo its changes. I choose the transaction boundary before choosing the batch size. Smaller commits reduce the amount of work belonging to one transaction, but they do not make DELETE an unlogged operation.
Define a Fixed Retention Boundary
Start with an approved retention rule and a fixed cutoff. Do not recompute a moving cutoff inside the loop. Confirm that the rule excludes legal holds, pending business processes, and data required by related tables. The following lab table contains only synthetic input and can be created in an existing disposable database.
CREATE TABLE dbo.EventHistory
(
EventID bigint NOT NULL PRIMARY KEY,
CreatedAt datetime2(0) NOT NULL,
Payload nvarchar(100) NOT NULL
);
INSERT dbo.EventHistory(EventID,CreatedAt,Payload)
VALUES(1,'20240101',N'Expired example'),
(2,'20240102',N'Another expired example'),
(3,'20260101',N'Retained example');
CREATE INDEX IX_EventHistory_CreatedAt
ON dbo.EventHistory(CreatedAt,EventID);The index supports finding eligible rows without repeatedly scanning unrelated newer rows. Its usefulness depends on the actual predicate and distribution. Additional indexes still require maintenance when rows disappear. Review the actual plan on a representative restored copy, including constraint and trigger work, rather than judging the operation from the table's row count alone.
Record the cutoff and target identity with the run. A restart can use the same predicate because committed rows are already absent. If older records can arrive while the job runs, decide whether the run includes those arrivals or uses an additional captured boundary. That choice belongs to the retention contract.
Commit Each Batch When Deleting Millions of Rows
The sample rejects an existing transaction so its commits really release each batch. It captures @@ROWCOUNT immediately after DELETE, before another statement replaces the value. The small batch size is a lab input, not a recommendation for every production table.
SET XACT_ABORT ON;
IF @@TRANCOUNT<>0
THROW 50000,'Run the retention loop outside an existing transaction.',1;
DECLARE @Cutoff datetime2(0)='20250101';
DECLARE @BatchSize int=1000,@Deleted int=1;
DECLARE @TotalDeleted bigint=0;
WHILE @Deleted>0
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
DELETE TOP (@BatchSize)
FROM dbo.EventHistory
WHERE CreatedAt<@Cutoff;
SET @Deleted=@@ROWCOUNT;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF XACT_STATE()<>0 ROLLBACK TRANSACTION;
THROW;
END CATCH;
SET @TotalDeleted+=@Deleted;
SELECT @Deleted AS BatchDeleted,@TotalDeleted AS TotalDeleted;
IF @Deleted>0 WAITFOR DELAY '00:00:01';
END;On the lab table, the first pass removes both expired rows and the second pass reports zero, which ends the loop. DELETE TOP does not establish a particular row order. This example needs only eventual removal of all qualifying lab rows. Where ordered progress is part of the requirement, select a bounded key set through an ordered CTE and delete that set. Do not assume that physical storage order supplies a stable checkpoint.
The delay is optional pacing. Measure application latency and batch duration, then choose an appropriate limit and pause. I keep cancellation between committed batches as a supported operating action. Already committed deletes remain committed, which means the recovery plan must account for partial completion rather than promise one rollback of the entire run.
Allow Log Reuse Between Transactions
Committing creates opportunities for reuse, but other requirements can still retain the log. Inspect the recovery model and current reuse wait. Under full recovery, keep the established log-backup schedule running. Under simple recovery, checkpoints participate in reuse. Neither action guarantees reuse when another active requirement holds log records.
SELECT name,recovery_model_desc,log_reuse_wait_desc
FROM sys.databases WHERE database_id=DB_ID();
SELECT total_log_size_in_bytes,used_log_space_in_bytes,
used_log_space_in_percent
FROM sys.dm_db_log_space_usage;If the wait identifies an active transaction or delayed replica, investigate that cause. Taking another full backup does not replace the missing log backups. Do not switch recovery models merely to get through a cleanup, because that changes the recovery contract. Pre-size appropriate log capacity and preserve disk headroom before the first delete starts.
For deleting millions of rows, log monitoring must accompany the loop rather than follow an out-of-space error. Collect usage and elapsed batch time between commits through the approved monitor. Stop or reduce the rate when agreed thresholds are reached. A batch loop has excellent persistence and absolutely no sense of business priorities.
Validate Progress After Deleting Millions of Rows
Count the remaining qualifying rows after the run and inspect retained boundary rows. Keep the filter identical to the approved rule. Large validation counts can themselves be expensive, so schedule them deliberately and use the supporting index.
DECLARE @Cutoff datetime2(0)='20250101';
SELECT COUNT_BIG(*) AS RemainingEligible
FROM dbo.EventHistory WHERE CreatedAt<@Cutoff;
SELECT EventID,CreatedAt,Payload
FROM dbo.EventHistory WHERE CreatedAt>=@Cutoff;Check dependent data and trigger outcomes as well. A reported batch count describes the target statement, not every cascaded deletion or external effect. Which business process needs these rows after the apparent retention date? Answer that question before approving the predicate. Performance tuning cannot repair a retention rule that deletes the wrong evidence.
Choose a Better Retention Operation When Available
If the entire eligible table can be emptied, TRUNCATE TABLE uses allocation logging rather than deleting each row individually. It has different permission, foreign-key, identity, and trigger behavior. It is transactional, not an instruction to bypass the log. Verify that its whole-table semantics match the requirement.
A partitioned retention design can switch a complete expired partition into a compatible staging table. Alignment, indexes, constraints, and the empty target must satisfy the switch requirements. Switching moves ownership of data rather than erasing it; the staging table still needs an approved archive or disposal action. Rehearse the full sequence with the actual schema.
Finish With an Operating Record
Set a maximum run duration and an explicit stop condition alongside the batch limit. Consider the busiest service period, replica catch-up, and the space required by unrelated transactions. A maintenance job should yield when the agreed operating conditions change. Retain its progress record outside the table being cleared so the next run can explain what already happened.
Keep the accepted cutoff, committed count, start and end times, failures, and validation result. If the run stops, record its partial completion and the reason before restarting. Avoid routine shrinking afterward when the same workload will need the log space again.
Deleting millions of rows works best as a controlled retention process with bounded transactions and visible progress. Select the simplest operation that matches the data boundary, then verify both application behavior and recoverability. Completing the loop is only one part of completing the maintenance task.
Related reading on this blog: Delete Statement and Index Usage and How to Solve Error When Transaction Log Gets Full? Interview Question of the Week #272.
A batch delete is not a way to avoid logging, it is a way to control transaction size and operational impact.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
