Deleting millions of records from a table without blowing the transaction log

Over the weekend I realized that deleting 80% of the records on a table with 87 million rows is not quite as easy as issuing a delete statement with a where criteria.

The main reason is that the transaction log quickly grows as it needs to keep track of all the uncommitted rows that are being deleted. In my case the database file was 60GB and the log file grew quickly from a mere 40MB to 32GB before I ran out of disk space and the database goes into recovery mode.

Browsing through blogs I narrowed my scenario to two options.

  1. Move the required records over to a temporary table and issue a truncate on the table you want to delete.
  2. Issue the deletes in batches so that the log file doesn’t fill up.

The first option seems to take the shorter time to execute but I didn’t want to go through the effort of removing and adding foreign keys  and renaming tables. So I went with the second option. Here’s the script I used for SQL Server which deletes 10,000 rows at a time and commits them.

DECLARE @continue INT
DECLARE @rowcount INT
 
SET @continue = 1
WHILE @continue = 1
BEGIN
    PRINT GETDATE()
    SET ROWCOUNT 10000
    BEGIN TRANSACTION
    DELETE FROM Transactions WHERE  TradeDate IS NULL
    SET @rowcount = @@rowcount 
    COMMIT
    PRINT GETDATE()
    IF @rowcount = 0
    BEGIN
        SET @continue = 0
    END
END

The print statements help to show you how things are moving along.

(10000 row(s) affected)

Jan  1 2009 11:54PM

Jan  1 2009 11:54PM

(10000 row(s) affected)

Jan  1 2009 11:55PM

Jan  1 2009 11:55PM

Seven hours later the script is still executing with 39 million rows deleted so far and the log file currently at 700MB.

Deleting millions of records from a table without blowing the transaction log
  • Marc

    You are my hero. Thank you for this 🙂

  • Vlad

    Awesome!!