Technical Article

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

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

Share

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

Related content

SQLServerCentral Article

A Normalization Primer

For most DBAs, normalization is an understood concept, a bread and butter bit of knowledge. However, it is not at all unusual to review a database design by a development group for an OLTP (OnLine Transaction Processing) environment and find that the schema chosen is anything but properly normalized. This article by Brian Kelley will give you the core knowledge to data model.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

5 (3)

You rated this post out of 5. Change rating

2003-01-13

18,595 reads