TL;DR
For large retention or cleanup jobs, avoid pre-numbering the entire candidate set. Delete a bounded number of qualifying rows, capture @@ROWCOUNT, commit the batch, and repeat until no rows remain.
DECLARE @BatchSize int = 5000;
DECLARE @RowsDeleted int = 1;
DECLARE @Cutoff datetime2(3) =
DATEADD(day, -90, SYSUTCDATETIME());
WHILE @RowsDeleted > 0
BEGIN
DELETE TOP (@BatchSize)
FROM dbo.Logs
WHERE CreatedUtc < @Cutoff;
SET @RowsDeleted = @@ROWCOUNT;
END;The key idea
Batch deletion is not about UI-style pagination. It is about bounding the amount of work performed in a single transaction.
Why one large DELETE is risky
Deleting millions of rows in one statement can be perfectly valid in an offline or lightly loaded system. In a production database, however, it can also create several operational problems.
Transaction log pressure
SQL Server records data modifications in the transaction log. A large delete therefore generates log activity and may keep a significant portion of that log active until the transaction commits.
Longer lock duration
Large modifications can acquire and hold many locks. The longer a transaction remains open, the longer concurrent workloads may have to wait.
Expensive rollback
If a very large transaction fails or is cancelled, SQL Server must undo the work. With bounded batches, already committed batches remain complete and only the current batch needs to roll back.
Important
Batching does not make DELETE unlogged. It reduces the transactional scope of each unit of work.
What batch deletion actually changes
Compare the two execution models:
Single large DELETE vs bounded batch DELETE
The second pattern keeps each transaction bounded. That makes the operation easier to throttle, monitor, stop, retry, and reason about.
Create a realistic sample table
CREATE TABLE dbo.Logs
(
LogId bigint IDENTITY(1, 1) NOT NULL,
[Text] nvarchar(max) NULL,
Severity varchar(16) NULL,
CreatedUtc datetime2(3) NOT NULL,
CONSTRAINT PK_Logs
PRIMARY KEY (LogId)
);For a new schema, prefer datetime2 over the older datetime type.
INSERT INTO dbo.Logs ([Text], Severity, CreatedUtc)
VALUES
('Lorem ipsum 1', 'Error', DATEADD(day, -120, SYSUTCDATETIME())),
('Lorem ipsum 2', 'Warning', DATEADD(day, -110, SYSUTCDATETIME())),
('Lorem ipsum 3', 'Info', DATEADD(day, -100, SYSUTCDATETIME())),
('Lorem ipsum 4', 'Warning', DATEADD(day, -95, SYSUTCDATETIME())),
('Lorem ipsum 5', 'Warning', DATEADD(day, -91, SYSUTCDATETIME())),
('Lorem ipsum 6', 'Error', DATEADD(day, -80, SYSUTCDATETIME())),
('Lorem ipsum 7', 'Warning', DATEADD(day, -70, SYSUTCDATETIME())),
('Lorem ipsum 8', 'Warning', DATEADD(day, -60, SYSUTCDATETIME())),
('Lorem ipsum 9', 'Info', DATEADD(day, -30, SYSUTCDATETIME())),
('Lorem ipsum 10', 'Info', SYSUTCDATETIME());Generate a Large Test Dataset
Ten sample rows are useful for understanding the schema, but they are not enough to demonstrate how a large purge behaves. Before testing the batch-delete script, populate the table with a configurable number of rows.
Default test size: 1,000,000 rows
Change only @RowCount to generate a smaller or larger dataset. Run this on a development or disposable test database, not against a production table.
The following set-based generator avoids executing one INSERT statement per row. It creates a sufficiently large row source with a CROSS JOIN, assigns sequential numbers with ROW_NUMBER(), and inserts the requested number of test records in one statement.
DECLARE @RowCount int = 1000000;
;WITH Numbers AS
(
SELECT TOP (@RowCount)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Number
FROM sys.all_objects AS A
CROSS JOIN sys.all_objects AS B
)
INSERT INTO dbo.Logs
(
[Text],
Severity,
CreatedUtc
)
SELECT
CONCAT('Log entry ', Number),
CASE Number % 3
WHEN 0 THEN 'Error'
WHEN 1 THEN 'Warning'
ELSE 'Info'
END,
DATEADD(
minute,
-Number,
SYSUTCDATETIME()
)
FROM Numbers;Change the number of generated rows
For one million rows:
DECLARE @RowCount int = 1000000;For five million rows:
DECLARE @RowCount int = 5000000;The generator deliberately spreads CreatedUtc values backwards in time. That gives the later retention predicate a meaningful distribution of old and recent rows instead of creating a million records with almost identical timestamps.
Verify the generated dataset
SELECT
COUNT_BIG(*) AS TotalRows,
MIN(CreatedUtc) AS OldestRowUtc,
MAX(CreatedUtc) AS NewestRowUtc
FROM dbo.Logs;If you ran the earlier ten-row sample inserts first, the total will be 1,000,010 rather than exactly one million. For a clean large-scale test, create the table and run only the generator before creating the supporting index and executing the purge.
Why not use a WHILE loop for test-data generation?
A loop that performs one million individual INSERT operations adds unnecessary overhead to test setup. A set-based generator is a better fit when the objective is to create volume quickly and then measure the behavior of the deletion strategy.
Index the rows you intend to delete
Batch size cannot compensate for a poor access path. If every batch repeatedly scans a huge table to locate a small number of qualifying rows, the purge can become progressively expensive.
CREATE INDEX IX_Logs_CreatedUtc_LogId
ON dbo.Logs (CreatedUtc, LogId);The exact index depends on the real schema and workload. Do not create an index blindly for a one-off cleanup, but recurring retention jobs should usually have an efficient way to locate the rows they remove.
Use a fixed cutoff and bounded batches
Define the cutoff once
DECLARE @Cutoff datetime2(3) =
DATEADD(day, -90, SYSUTCDATETIME());A fixed cutoff makes the purge predictable. Rows do not enter the candidate set merely because the job has been running for a long time.
Delete the oldest rows first
For retention jobs, deterministic oldest-first processing is easier to reason about:
DECLARE @BatchSize int = 5000;
DECLARE @RowsDeleted int = 1;
WHILE @RowsDeleted > 0
BEGIN
;WITH DeleteBatch AS
(
SELECT TOP (@BatchSize)
LogId
FROM dbo.Logs
WHERE CreatedUtc < @Cutoff
ORDER BY CreatedUtc, LogId
)
DELETE L
FROM dbo.Logs AS L
INNER JOIN DeleteBatch AS B
ON B.LogId = L.LogId;
SET @RowsDeleted = @@ROWCOUNT;
END;Using LogId as a secondary ordering key gives deterministic ordering when multiple rows share the same timestamp.
Choose the right batch size
There is no universal batch size. The right value depends on:
row size;
number of indexes;
foreign keys and triggers;
current workload;
transaction-log throughput;
storage performance;
acceptable blocking duration.
Values such as 1000 or 5000 are useful starting points for many conventional tables. They are not magic constants.
Optional throttling
On a busy production system, a short delay between batches can deliberately trade total runtime for lower sustained pressure:
IF @RowsDeleted > 0
WAITFOR DELAY '00:00:00.100';Transaction boundaries matter
A common mistake is to wrap the entire loop in one transaction:
BEGIN TRANSACTION;
WHILE (...)
BEGIN
DELETE ...
END;
COMMIT TRANSACTION;That recreates one long-running transaction and removes one of the main reasons to batch in the first place.
Preferred transaction boundary
BEGIN TRAN → batch → batch → batch → COMMIT
batch → commit → batch → commit → batch → commit
Transaction log considerations
What batching can improve
smaller transactions;
shorter lock lifetime;
smaller rollback scope;
more manageable log activity over time.
What batching does not solve
it does not make DELETE unlogged;
it does not guarantee the physical log file shrinks;
it does not replace transaction-log backups;
it does not fix an incorrect recovery strategy;
it does not guarantee zero blocking.
FULL recovery model
If the transaction log continues growing even with small batches, inspect recovery-model behavior, transaction-log backups, long-running transactions, and log-reuse waits before assuming the batch size is the problem.
Deleting related records
When child records must be removed before parent rows, use one explicit transaction per batch and handle errors defensively.
BEGIN TRY
BEGIN TRANSACTION;
-- Delete child rows for the current batch.
-- Delete parent rows for the current batch.
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
ROLLBACK TRANSACTION;
THROW;
END CATCH;The principle is simple: atomic per batch, not necessarily atomic for the entire purge.
Monitoring and troubleshooting
Before changing the algorithm, determine what is actually limiting the purge:
Is the transaction log nearly full?
What recovery model is being used?
Are transaction-log backups running?
Is another long-running transaction preventing log reuse?
Is the purge being blocked?
Is the purge itself blocking application requests?
Is every batch scanning a large portion of the table?
Are triggers or foreign keys adding work?
Add progress reporting
DECLARE @TotalDeleted bigint = 0;
SET @RowsDeleted = @@ROWCOUNT;
SET @TotalDeleted += @RowsDeleted;
PRINT CONCAT(
'Deleted ',
@RowsDeleted,
' rows in this batch. Total deleted: ',
@TotalDeleted
);Production-ready batch deletion script
This is the version I would use as the starting point for a production retention job:
SET NOCOUNT ON;
SET XACT_ABORT ON;
DECLARE @BatchSize int = 5000;
DECLARE @RowsDeleted int = 1;
DECLARE @TotalDeleted bigint = 0;
DECLARE @Cutoff datetime2(3) =
DATEADD(day, -90, SYSUTCDATETIME());
DECLARE @StartedUtc datetime2(3) =
SYSUTCDATETIME();
PRINT CONCAT(
'Batch delete started at ',
CONVERT(varchar(33), @StartedUtc, 126)
);
PRINT CONCAT(
'Deleting rows older than ',
CONVERT(varchar(33), @Cutoff, 126)
);
PRINT CONCAT(
'Batch size: ',
@BatchSize
);
WHILE @RowsDeleted > 0
BEGIN
;WITH DeleteBatch AS
(
SELECT TOP (@BatchSize)
LogId
FROM dbo.Logs
WHERE CreatedUtc < @Cutoff
ORDER BY CreatedUtc, LogId
)
DELETE L
FROM dbo.Logs AS L
INNER JOIN DeleteBatch AS B
ON B.LogId = L.LogId;
SET @RowsDeleted = @@ROWCOUNT;
SET @TotalDeleted += @RowsDeleted;
IF @RowsDeleted > 0
BEGIN
PRINT CONCAT(
'Deleted ',
@RowsDeleted,
' rows. Total: ',
@TotalDeleted
);
WAITFOR DELAY '00:00:00.100';
END;
END;
PRINT CONCAT(
'Batch delete finished at ',
CONVERT(varchar(33), SYSUTCDATETIME(), 126)
);
PRINT CONCAT(
'Total rows deleted: ',
@TotalDeleted
);Safety check before DELETE
DECLARE @Cutoff datetime2(3) =
DATEADD(day, -90, SYSUTCDATETIME());
SELECT TOP (100)
LogId,
Severity,
CreatedUtc
FROM dbo.Logs
WHERE CreatedUtc < @Cutoff
ORDER BY CreatedUtc, LogId;For destructive maintenance, validating the predicate is worth far more than saving a few seconds.
DELETE vs TRUNCATE
Requirement | DELETE | TRUNCATE |
|---|---|---|
Remove selected rows | Yes | No |
Use WHERE | Yes | No |
Remove all rows | Yes | Yes |
Suitable for retention purge | Yes | No |
Fast full-table reset | Usually not | Often yes |
Foreign-key restrictions | Normal RI rules | Stricter restrictions |
If the intent is genuinely to remove every row and the table satisfies the required restrictions:
TRUNCATE TABLE dbo.Logs;Do not treat foreign-key removal casually
Dropping constraints only to make TRUNCATE work is a schema change with operational consequences. Prefer a deliberate integrity-preserving cleanup strategy.
Official References
The recommendations in this article are grounded in current Microsoft documentation for SQL Server and Azure SQL. Use these references when validating behavior for your SQL Server version, recovery model, or production environment.
Microsoft Learn reference | Why it matters |
|---|---|
Microsoft guidance on lock escalation and reducing large DELETE operations into smaller batches. | |
Explains SQL Server locking, transaction behavior, concurrency, and why transaction scope matters. | |
Core documentation for transaction logging, active log records, truncation, and log reuse. | |
Defines SIMPLE, FULL, and BULK_LOGGED recovery models and how they affect transaction-log maintenance. | |
Important for databases using FULL or BULK_LOGGED recovery, where regular log backups are part of normal log management. | |
Covers monitoring, sizing, growth, and appropriate transaction-log file management. | |
Helps identify why log space cannot be reused instead of assuming batch size is the root cause. | |
Authoritative reference for TRUNCATE behavior, identity reset, logging characteristics, permissions, and foreign-key restrictions. | |
Microsoft explicitly recommends avoiding | |
Reference for the higher-precision date/time type used by the examples in this article. | |
Official reference for the window function used to assign sequential values while generating the large test dataset. | |
Microsoft guidance on batching for Azure SQL Database and Azure SQL Managed Instance workloads. |
Reference policy
Prefer first-party Microsoft Learn documentation for SQL Server behavior, transaction semantics, recovery models, and platform-specific recommendations. Community posts can be useful for troubleshooting context, but they should not replace the product documentation for core engine behavior.
Production checklist
Confirm the exact deletion predicate.
Preview candidate rows with SELECT.
Estimate row count with COUNT_BIG when useful.
Confirm there is an appropriate access path or index.
Check foreign-key relationships.
Check triggers.
Understand the recovery model.
Verify transaction-log backups when using FULL recovery.
Check available transaction-log space.
Select a conservative initial batch size.
Decide whether throttling is necessary.
Do not wrap the entire purge in one transaction.
Monitor blocking and log utilization.
Record deleted-row counts and execution duration.
Test the process outside production first.
Frequently Asked Questions
Does deleting rows in batches reduce transaction log growth?
It limits the work performed in each transaction and can make log activity easier to manage. It does not make DELETE unlogged and it does not replace correct log-management practices.
What batch size should I use?
There is no universal value. Start conservatively, measure batch duration, blocking, CPU, I/O, and log behavior, then tune from real workload data.
Should I wrap all batches in one transaction?
Normally no. That recreates one long-running transaction. If multiple statements must be atomic, use one explicit transaction per batch.
Can batching completely eliminate blocking?
No. DELETE still modifies data and requires locking. Batching reduces transactional scope; it does not make writes non-blocking.
Does DELETE reduce the physical database file size?
Not automatically. Removing rows creates reusable space inside database structures. File-size management is a separate concern.
Should I use TRUNCATE instead?
Use TRUNCATE when you truly intend to remove every row and the table satisfies its restrictions. Use DELETE for retention rules, predicates, or normal row-removal semantics.
Can I safely restart a stopped purge?
Usually yes, when the predicate is stable and each completed batch commits independently. Rerunning the same retention rule simply continues with the remaining qualifying rows.
Key takeaways
Large data removal is primarily a transaction-management problem, not a pagination problem.
A good production purge
Find a small batch → delete it → commit it → measure → repeat.
Use a stable retention predicate, make sure SQL Server can locate candidate rows efficiently, understand the recovery model, and tune the batch size against the real production workload.
The best batch-delete script is not the one that removes data fastest. It is the one that removes it predictably without destabilizing the rest of the system.
