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 FROM OriginalDB.dbo.ProductionTable
OUTPUT DELETED.* INTO ArchiveDB.dbo.ArchiveTable --New DB in Simple mode
Answer by Oleg ·
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
Answer by Mark ·
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.