I agree with Ten Centuries on 'keep it simple'. Before I start this kind of operation, I have a backup of either the database or the table that I can restore if needed. I then usually switch the database to simple so I am not making huge transaction log backups. In most production environments there would not be time to track, organize and restore all of them.....

Because of the nature of most of these tables, lack of partitioning and other issues, deleting the top 10000 records will usually bring production to a screeching halt because of the locking blocking issues.

In most cases, I identify the group of records to delete by their clustered key and copy that clustered key value to a scratch table. I then set up a while loop to select top top xx records from my scratch table into a temp table, delete the records from the production table joined to the temp table by the clustered key, and then delete the records from the scratch table. I can do a count on the scratch table to tell me how many records I have to go at any time.

This makes for much more work but the benefits are that you can start your delete record size at a moderate, for your table, size. Monitor the database and server, check for locks and blocks and adjust the size of the individual deletes either up or down depending on the activity on the database and server. An additional benefit is that you can kill the operation at any time and if there is a rollback, it is only a small one, and you can just restart the code when you are ready to run again. In heavily used tables a wait of 1 to 5 seconds at the bottom of the loop is sometimes called for.

If production is up and running with no major delays and complaints about poor performance, does it really matter if you take 2 weeks to delete 200 + million records from the sales table.....

This is not a job that I walk away from, but monitor rather closely, and will turn off or on depending on the business needs. It's more work for me, but production is running, generating money so that I can be paid 🙂 and it gets the job done, which is what it is all about anyway...