Nibbling Deletes

Nibbling Deletes. I did not see where you address space requirements when doing large deletes during a archive process. The Transaction Log can grow considerably if not backed up often enough so a addon to your code could include within the loop or another outside loop a step that takes a tlog backup every so often to keep the tlog in check. Can you give an example of this in your code example and video? Putting the db into SIMPLE recovery mode during this operation is not normally a good idea.

Great point - if you're deleting large amounts of data, the log file WILL grow quite a bit.

But rather than manually backing up your log file as part of this process, I'd recommend just ensuring that you're regularly backing up your log file, making sure you've got enough space to handle a larger operation, and then just keeping an eye on the operation as you go. (Start by making a small number of deletes until you've got a good feel for what kinds of log space this will require.)

And yes, switching your DB into SIMPLE Recovery would typically be a bad idea.

instead of using

DELETE FROM LorryLocations
WHERE locationId IN (
SELECT TOP (@count) locationId
FROM LorryLocations WITH(NOLOCK)
WHERE
[timestamp] < @cutoff
)

why not use

DELETE TOP(@count) FROM LorryLocations
WHERE [timestamp] < @cutoff
)


And instead taking log backup why not shrink the log file

Please correct me if i m wrong

Thanks