SQL Server - Chunking Large Deletes

I’ve gotten a bit tired of one of our implementation guys always asking what to do about the database log file getting full and failing because of it. This happens occasionally during the nightly purge job, which deletes a day’s worth of old rows of data that we don’t need (usually a day from a week to 30 days ago, configurable as a parameter to the Stored Procedure).

Normally this is not a problem … our databases are set up with Simple Recovery, so the log file gets re-used and will usually not out-grow it’s allocated size. The only time it became a problem is on heavy-use days when the deletes were using up more than the log file’s allocated size. Another thing that occasionally caused it to happen is if the nightly purge didn’t run for some reason and then the next night, there would likely be twice as many rows of data to delete and then it became an endless cycle of failures … especially when the implementation guys don’t notice it happening and a week goes by without anything getting purged!!! Arrgghh!

Well, there is a solution to the problem and that is to chunk the deletes into more byte-size pieces (pun intended!) … in other words, “bulk” delete a smaller subset of data within a transaction, and continue in a loop until the entire set of data has all been deleted. Here’s the beauty of this methodology: with a Simple Recovery database, all you have to do is issue the CHECKPOINT command after the COMMIT TRANSACTION, and the log file use gets set back to the beginning of the log file. This can also be done with the Full Recovery database, but you issue a BACKUP LOG command instead.

Here’s a sample of the T-SQL that you’d put into your Stored Procedure. Note that I’m deleting all rows earlier than a specified date (and with a nightly job, that amounts to deleting one day’s worth of data).

-- @delay is passed into the SP as a parameter
IF @delay > 0
    SET @delay = -@delay

IF @delay < 0
BEGIN
    DECLARE @time datetime
    DECLARE @rc INT

    SET @time = DATEADD(day, @delay, GETDATE())
    
    -- delete from message
    SET @rc = 1
    WHILE @rc > 0
    BEGIN
        BEGIN TRANSACTION
            DELETE TOP (100000) message WHERE saveddatetime < @time
            SET @rc = @@ROWCOUNT
        COMMIT TRANSACTION
        CHECKPOINT
    END

    -- I have a bunch more deletes from other tables, each in the same format
    SET @rc = 1
    -- etc...
END

I didn’t figure this stuff out by myself. I found a really great post by Aaron Bertrand here: http://sqlperformance.com/2013/03/io-subsystem/chunk-deletes He posts a few nice charts comparing various combinations.

After reading that post, you might notice that the time it takes to perform these DELETEs could take a little longer using this method, but if all you care about is the size of the log file, it might not matter to you. One way to speed it up is to disable the indexes, do the deletes, enable the indexes. This is mentioned in the following blog post: http://shades-of-orange.com/post/2014/05/28/Delete-a-Large-Number-of-Rows-from-a-Table-in-SQL-Server  The only caveat that I can think of when messing with the indexes is that, depending on how many indexes you have to enable after you’re done, it may take a bit of time to re-index the data. In the blog post, the writer says it took an extra minute, but YMMV. I opted not to mess with the indexes … for me, it was all about the log file size, not the time it was taking.