Advise on deleting huge amount of rows

Question

question

Slick84 asked Jun 7, '10

Advise on deleting huge amount of rows

Hi,

I need to delete about 150 million rows from a table on a production server.

I have created a archive database in which i will copy the output of the deleted rows as well.

However, since this is such a huge amount of rows that need to be deleted, I need to know what would be the wisest way of going on about doing this?

We backup our transaction logs for the database that I need to delete from every half hour. I know deleting all of these rows in one go would grow the transaction log file real quick. I'm thinking of taking a multi-step approach by deleting 5% of rows in one go then letting the transaction log backup at the half-hour point so its truncated and then run again and so on.

Any recommendations/advice? Thank you in advance.

Delete query:

DELETE FROM OriginalDB.dbo.ProductionTable
OUTPUT DELETED.* INTO ArchiveDB.dbo.ArchiveTable --New DB in Simple mode
WHERE DateDiff(day,GETDATE(),DateTimeColumn)<-1372

Regards,
-S

t-sql transaction-log delete

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-06-07T14:05:13Z

Oleg answered Jun 7, '10

I added the activity reporting logic per Henrik suggestion.

What I have seen helped in the past is exactly what you suggested, i.e. restricting the number of deleted records such that every transaction is not huge yet the total number of transaction is still reasonable. Finding such the number is not exact science, but can be found depending on your settings. For example, suppose you restrict it to 1,000,000 records. Even if each delete will take say 20-30 seconds, you can still complete the whole task with only one (maybe two) log backup(s) in between, i.e.

declare @rowcount int;
declare @step_number int;
declare @message varchar(128);

select @rowcount = 1, @step_number = 0, @message = '';

while (@rowcount > 0)
begin    
    begin tran;

    delete top (1000000) from OriginalDB.dbo.ProductionTable
    output deleted.* into ArchiveDB.dbo.ArchiveTable        
    where datediff(day, getdate(), DateTimeColumn) < -1372;

    set @rowcount = @@rowcount;

    select 
            @step_number = @step_number + 1, 
            @message = 'Step: ' + cast(@step_number as varchar(10)) + 
            '; Rowcount: ' + cast(@rowcount as varchar(10)) + 
            '; Time stamp: ' + convert(varchar(19), getDate(), 121);

    raiserror(@message, 10, 1) with nowait;

    commit tran;
end;
go

Oleg

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Henrik Staun Poulsen commented · Jun 07, 2010 at 03:50 PM

This is where I would also add this bit of code:
DECLARE @mydate DATETIME = GETDATE(), @d VARCHAR(19)
SET @d = CONVERT(VARCHAR(19), @mydate, 121)
RAISERROR('ugly way to print dates : %s', 10, 1, @d) WITH NOWAIT
so that I can see how far the program is.

2 ·

Answer 2 · 2010-06-07T15:24:29Z

Mark answered Jun 7, '10

Depending on the circumstances, if the number of records you want to keep is small, it may be easier to copy out the records you want to a new table, then renaming the old table and the new table so that the new one only has the records you want. Now if there are lot of indexes, triggers, etc. that you have to worry about, it may not be worth it.

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Slick84 commented · Jun 09, 2010 at 11:41 AM

Thanks for your input Mark.

0 ·

question