SQL Server: batched delete and update with commit in a loop
T-SQL patterns to purge or modify large tables in batches, with commit, pause and progress monitoring.
Purging or updating millions of rows in one transaction bloats the transaction log and holds locks for a long time. This article shows T-SQL patterns that work in small batches, commit after each batch, wait briefly between batches and print progress, plus queries to monitor the progress.
Batch delete with commit per loop
Deletes rows older than a cut-off date from a log table, 100,000 rows at a time, until nothing is left to delete. A cascade is configured from the parent table to dbo.CategoryLog, so the child rows are removed automatically.
Declare @iMonth int
DECLARE @tInteraction TABLE (ref_Id INT ,interaction_Id CHAR(21))
DECLARE @tTransaction TABLE (interaction_Id char(21),transaction_Id int)
DECLARE @iIteration INT
DECLARE @iRowCount INT
DECLARE @sMessage VARCHAR(200)
DECLARE @start_time DATETIME
DECLARE @end_time DATETIME
DECLARE @elasted_time INT
SET @iIteration = 0;
SET @end_time = DATEADD(hour,-3,SYSUTCDATETIME())
PRINT @end_time
SET @iRowCount = 1;
WHILE ( @iRowCount != 0 )
BEGIN
SET @iMonth = -1 /* Do not forget the minus */
BEGIN TRAN
SET @iIteration = @iIteration + 1
SET @sMessage = '@iIteration ' + CAST(@iIteration AS VARCHAR);
RAISERROR (@sMessage, 0, 1) WITH NOWAIT
SET @start_time = GETDATE()
SET @iRowCount = 0
;WITH CTE AS
(
SELECT TOP
100000 *
FROM
[dbo].[Log]
WHERE
timestamp < @end_time
ORDER BY LogID ASC
)
DELETE FROM CTE
SET @iRowCount= @iRowCount + @@RowCount
--CategoryLog
/*
a cascade has been configured
select count(*)
from dbo.CategoryLog cl
where not exists ( select [LogID] from [dbo].[Log] l where l.LogID = cl.LogID)
delete dbo.CategoryLog
from dbo.CategoryLog cl
where not exists ( select [LogID] from [dbo].[Log] l where l.LogID = cl.LogID)
*/
COMMIT
WAITFOR DELAY '00:00:05';
SELECT @elasted_time= RTRIM(CAST(DATEDIFF(MS, @start_time, GETDATE()) AS CHAR(10)))
SET @sMessage = '@iRowCount ' + CAST(@iRowCount AS VARCHAR) + ' Elapsed time : '+ CAST( @elasted_time AS VARCHAR);
RAISERROR (@sMessage, 0, 1) WITH NOWAIT
END
GO
RAISERROR ... WITH NOWAIT flushes the message immediately, so progress is visible in SSMS while the loop runs. The WAITFOR DELAY between batches lets other sessions and log backups work.
Check for orphan child rows after the purge:
select count(*)
from dbo.CategoryLog cl
where not exists (select [LogID] from [dbo].[Log] l where l.LogID = cl.LogID)
Monitor the progress
Row counts from the partition statistics (fast, no table scan):
SELECT OBJECT_NAME(object_id), SUM(row_count) AS rows
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('CategoryLog')
AND index_id < 2
GROUP BY OBJECT_NAME(object_id);
SELECT OBJECT_NAME(object_id), SUM(row_count) AS rows
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('Log')
AND index_id < 2
GROUP BY OBJECT_NAME(object_id);
Oldest and newest entries, and rows per category:
select min([timeStamp]),max([timeStamp]),count(*)
from dbo.log;
SELECT count(*), CategoryName
FROM [AppLoggingDb].[dbo].[Log] l
inner join [dbo].[CategoryLog] cl on l.[LogID] = cl.[LogID]
inner join [dbo].[Category] c on c.[CategoryID] = cl.[CategoryID]
group by CategoryName
Volume per day and per hour:
SELECT CAST(timestamp as date) AS ForDate,
DATEPART(hour,timestamp) AS OnHour,
COUNT(*) AS Totals
FROM [AppLoggingDb].[dbo].[Log]
GROUP BY CAST(timestamp as date),
DATEPART(hour,timestamp)
order by ForDate, OnHour
Record the row count over time
Create a table that stores the row count at a regular interval:
create table dbo.increaseLog (id int identity(1,1) primary key not null, SchemaName varchar(1000), TableName varchar(1000), timeStamp datetime2)
ALTER TABLE dbo.increaseLog ADD count bigint NULL
Collector loop (one sample per minute for 300 minutes):
DECLARE @StartDate AS DATETIME
DECLARE @EndDate AS DATETIME
DECLARE @CurrentDate AS DATETIME
DECLARE @minute int
SET @StartDate = GETDATE()
SET @minute = 300
SET @EndDate = DATEADD(Minute,@minute,getdate())
SET @CurrentDate = @StartDate
WHILE (@CurrentDate < @EndDate)
BEGIN
insert into dbo.increaseLog (SchemaName,TableName,timeStamp,count)
SELECT
s.name AS 'SchemaName'
,o.name AS 'TableName'
,getdate()
,SUM(p.row_count) AS 'RowCount'
FROM sys.dm_db_partition_stats p
JOIN sys.objects o ON o.object_id = p.object_id
JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE p.index_id <2 AND o.type = 'U'
AND s.name = 'dbo'
AND o.name = 'Log'
GROUP BY s.name,o.name
ORDER BY s.name,o.name;
WAITFOR DELAY '00:01:00';
RAISERROR('loop', 0, 1) WITH NOWAIT;
set @CurrentDate = getdate();
END
Read the evolution (seconds between two samples and rows added or removed):
SELECT
[timeStamp]
,DATEDIFF(second,[timeStamp], lag([timeStamp],1,getdate()) OVER (ORDER BY id desc) ) as second
,[count] as tableCount
,( lag([count],1,0) OVER (ORDER BY id desc)-[count] ) AS NewRows
FROM [AppLoggingDb].[dbo].[increaseLog]
order by [id] desc
Batch update with a cursor and commit per batch
Same pattern for an UPDATE: here, trimming trailing spaces from an identifier column, 10,000 rows at a time, for each distinct value selected by a cursor.
DECLARE @BatchSize INT = 10000;
DECLARE @RowCount INT;
DECLARE @Iteration INT;
DECLARE @StartTime DATETIME;
DECLARE @ElapsedTime INT;
DECLARE @Message VARCHAR(200);
DECLARE @CurrentId VARCHAR(50); -- Adjust the data type according to your schema
-- Declare a cursor to iterate through each distinct identifier
DECLARE IdCursor CURSOR FOR
SELECT DISTINCT EntityId
FROM dbo.[LinkedEntity]
WHERE EntityId LIKE '% ' and ltrim(rtrim(EntityId)) in ('12345'); -- Add your filtering criteria if necessary
OPEN IdCursor;
FETCH NEXT FROM IdCursor INTO @CurrentId;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Iteration = 0;
SET @RowCount = 1; -- Initialize to a value greater than 0 to enter the loop
RAISERROR ('Processing EntityId: %s', 0, 1, @CurrentId) WITH NOWAIT;
-- Batch processing for the current identifier
WHILE (@RowCount > 0)
BEGIN
BEGIN TRAN;
SET @Iteration = @Iteration + 1;
SET @Message = 'EntityId: ' + @CurrentId + ' | Iteration: ' + CAST(@Iteration AS VARCHAR);
RAISERROR (@Message, 0, 1) WITH NOWAIT;
SET @StartTime = GETDATE();
-- Update in batches of @BatchSize rows for the current identifier
WITH CTE AS
(
SELECT TOP (@BatchSize) *
FROM dbo.[LinkedEntity]
WHERE EntityId = @CurrentId
AND EntityId LIKE '% ' -- Additional conditions if necessary
)
UPDATE CTE
SET EntityId = LTRIM(RTRIM(EntityId));
SET @RowCount = @@ROWCOUNT;
COMMIT;
SET @ElapsedTime = DATEDIFF(MILLISECOND, @StartTime, GETDATE());
SET @Message = 'Rows updated: ' + CAST(@RowCount AS VARCHAR)
+ ' | Elapsed time: ' + CAST(@ElapsedTime AS VARCHAR) + ' ms';
RAISERROR (@Message, 0, 1) WITH NOWAIT;
-- Wait for a short delay to reduce contention
WAITFOR DELAY '00:00:05';
END
FETCH NEXT FROM IdCursor INTO @CurrentId;
END
CLOSE IdCursor;
DEALLOCATE IdCursor;
RAISERROR ('Processing completed for all identifiers.', 0, 1) WITH NOWAIT;
Notes
- Tune the batch size (100,000 for the delete, 10,000 for the update) and the delay to your log throughput and contention level.
- Table, column and database names above are generic examples; adapt them to your schema.