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.

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

WHILE @rc > 0
BEGIN
BEGIN TRANSACTION;

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

SET @rc = @@ROWCOUNT;

COMMIT TRANSACTION;

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

END

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s