5

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.

1

The most important thing when doing a large update or delete is to avoid the transaction log growing out of control. To allow log reuse & avoid bloating to Tlog (Transaction log truncation)

  • In full recovery mode - take log backups
  • In Simple recovery mode - do checkpoints.

Since you are using Full recovery, your delete logic should be

begin
 begin tran
   -- delete using batches 
   -- carefully choose batch size. SQL Server will do a lock escalation 
   -- for batch greater than 5000. (https://technet.microsoft.com/en-us/library/ms184286(v=sql.105).aspx)
 commit tran / rollback tran 
  -- since you are using Full recovery, take log backups
  -- with CHECKPOINT SQL Server still cannot reuse those portions of the log until you perform a log backup.
end

I use waitfor delay to esp. avoid prolonged blocking.

Refer to this gold mine blog post - Break large delete operations into chunks by Aaron Bertrand. Also, Take Care When Scripting Batches is very useful blog post.

  • Excellent sources. I see that your response and cody-konior's response somewhat contradict each other in regards to the WAITFOR but based on your response and the content of the sources I'm accepting your answer. Thank you – Alf47 Mar 6 '17 at 16:45
1

The two are equivalent and you're confusing batching with log truncation.

The important thing is that the code runs in batches so that locks get released and other sessions can jump in and read the table (and also so the log file can clear when the database is in in the simple recovery model).

However - in your first example the WAITFOR likely isn't necessary - all of the sessions are queued up waiting and will likely get in between each run.

The second is equivalent except it tells SIMPLE mode databases that the log can be cleared, however, this is likely to happen every few seconds anyway! In FULL recovery model neither is adding much value.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.