LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon