Typically, when one of the developers or data analysts needs to perform a very large update or deletion of data (where a truncate or truncate/insert wouldn't make sense because the data set to keep is too large) I recommend to them to do something like the following:
-- Delete 1 million rows 1 thousand at a time DELETE TOP (1000) FROM TableA WHERE <condition> WAITFOR DELAY '00:00:01' GO 1000
The result of this on databases that are in full recovery mode is that 1) the wait allows other transactions to process if needed and 2) when the log backup runs it is able to mark the operations already completed as dirty in the log file so it can re-use the space and prevent the log from growing too rapidly.
Rather than doing this, I'd like to know if it's possible to accomplish the same thing using checkpoints. Would this statement effectively result in the same situation from happening?
-- Delete 1 million rows 1 thousand at a time WHILE EXISTS ( SELECT 1 FROM TableA WHERE <condition> ) BEGIN DELETE TOP (1000) FROM TableA WHERE <condition> WAITFOR DELAY '00:00:01' CHECKPOINT END
Again, these are databases in full recovery mode.