Divide big delete into several small ones

,

Big transactions cause the Tlog to grow.
When you do a clean of a large table, the delete statement can cause a very long transaction
Sometimes it's necessary to prevent this.
So we divide one big delete into several little ones.

A table with call centre data gets +/- 1-mlj records a day. Every day we run a script to delete records with date less than one month ago

To prevent big transactions we do a commit every 1000 records. This is slower we know that but it keeps the Tlog small.

-- Delete records with every 1000 deletes a commit

declare @loop int, @tel int

select @loop =  count(*) / 1000 + 1 from demo
set @tel = 0
while  @tel < @loop
begin
    begin transaction
	    delete demo from (select top 1000 * from demo) as T1 where demo.date < dateadd(m,-1,getdate())
    commit transaction
    set @tel = @tel + 1
end

Rate

Share

Share

Rate