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