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:
- It will not create one big transaction.
- It avoids a table lock.
- 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.
set nocount on;
set @rc = 1;
WHILE @rc > 0
DELETE TOP (50000) –you need validate batch size
SET @rc = @@ROWCOUNT;
CHECKPOINT; –if simple recovery model, if it’s full than log backup