Deleting Records from a Large Table

If you need to delete a large number of records based upon a certain criteria (ie. you can't use TRUNCATE TABLE) then you may have come across the problem where the transaction log fills up and you may run out of disk space and consequently the DELETE query fails and nothing is deleted.

The Problem:

A DELETE executes as a transaction where it will not commit until the last record is deleted. DELETE physically removes rows one at a time and records each deleted row in the transaction log. If the number of records in a table is small, a straight out DELETE is fine, however, with a large table the DELETE will cause the transaction log to grow. When this happens the system IO performance is degraded. The person executing the DELETE query will usually panic because what seems to be a simple task takes a long time and there is no visible progress from Query Analyzer. If the transaction log runs out of disk space then the DELETE fails and the transaction rolls back and nothing ends up deleted.

A Solution:

A solution is to use the combination of a WHILE loop and @@ROWCOUNT to delete the records in manageable blocks (or chunks)
In this way the transaction log will only grow by the number of records you specify for ROWCOUNT (Default 100000). Once the first 100000 is deleted, the transaction is committed and the transaction log empties (not physically) and the next 100000 deletions will fill the empty transaction log usually by the same amount so the transaction log does not physically grow again.

Below is a sample script that you can use to do bulk deletions.
Just change the FROM and WHERE statements to fit your requirements and set ROWCOUNT accordingly.

Please be aware that during the DELETE operation, the table will be locked, so any process trying to read or write to the table will need to wait most probably until ALL the selected records are deleted. If you are concerned about this you can use WAITFOR to wait however long you like before deleting the next batch of records.

Conditions:

The script will work only if:

  1. The database Recovery Model is Simple (not Full).
  2. It's on a local table only. This is because setting of the SET ROWCOUNT option is ignored for DELETE statements against remote tables, local and remote partitioned views.

Download/Script:

Deleting_records_from_a_large_table.zip

-- Deleting records from a large table.
-- NOTE: This will not work on remote tables, local and remote partitioned views.

SET ROWCOUNT 100000 -- Or what ever your system can handle.

WHILE 1 = 1
BEGIN
    PRINT CONVERT(varchar(19), GETDATE(), 120) + ' Start'

    DELETE
    FROM YourTable
    WHERE SomeField = xxx
    
    IF @@ROWCOUNT = 0 BREAK

    PRINT CONVERT(varchar(19), GETDATE(), 120) + ' Finished.'

    -- A delay will help prevent locking/blocking if required.
    -- WAITFOR DELAY '00:02:00'
END

SET ROWCOUNT 0