question

Slick84 avatar image
Slick84 asked

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-sqltransaction-logdelete
10 |1200

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

Oleg avatar image
Oleg answered

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 comment
10 |1200

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 avatar image Henrik Staun Poulsen commented ·
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 Likes 2 ·
Mark avatar image
Mark answered

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 comment
10 |1200

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

Slick84 avatar image Slick84 commented ·
Thanks for your input Mark.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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