Delete millions of rows from table

Sometimes there will be the requirement to delete million rows from a table, as it is hard to run a single Delete statement because it could eventually fill up your transaction log and may not be truncated from log until all the rows have been deleted and the statement is completed because it will be treated as open transaction.

A safer, but much more time consuming way to delete millions of rows is to use some sort of looping mechanism, where you gradually delete a fairly small number of rows in a loop. This will take much longer than a set based operation, but, if done properly, will not cause concurrency problems, and will not overwhelm transactional replication or database mirroring.

This method has 3 advantages:

  1. It will not create one big transaction.
  2. It avoids a table lock.
  3. If the delete statement is canceled, only the last batch is rolled back. Records in previous batches are deleted.

So, here is the script. Enjoy.

@rc int;
set nocount on;
set @rc = 1;

WHILE @rc > 0

DELETE TOP (50000) –you need validate batch size
FROM <Table_Name>



CHECKPOINT;  –if simple recovery model, if it’s full than log backup



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s