How to delete millions of rows in batches

19 March,2016 by Jack Vamvas

Deleting millions of rows without enough transaction log space can cause all sorts of problems in trying to complete the deletion job. Most databases are sized with enough disk space to manage the cross- section of DML activity on the database. But there may be an ad-hoc requirement to delete a very large amount of rows , it may be planned such as an archive job or a data fix.

Whatever the reason , if you attempt to delete in one transaction , then the DELETE job will  fill the transaction log until the DELETE is commited. This applies whether you are in Full Recovery or Simple Recovery.   There are  differences between SIMPLE and FULL RECOVERY but that’s a different conversation.

There are plenty of workarounds to the transaction log growing and filling the disks. With a little bit of forward planning you may do such things as a) provision extra disk b) create extra transaction log files.  But you may find these steps don’t solve the problem.

Another option is to complete the DELETE in batches. By deleting in batches, the job is using smaller amounts of log space , commiting and flushing to disk. If the database is set in SIMLE or BULK LOAD, then you keep reusing the same space.

Here is an example of a code which deletes in batches. There is a variable @BatchSize , which you can set . You need to experiment to find what is suitable for the DELETE job within the environment.

Adjust the DELETE statement according to the requirements. The key point is to construct the sql statement in such a way that it exploits the @BatchSize.

 

 

use MyDB
GO
DECLARE @BatchSize INT = 4999
WHILE 1 = 1 
BEGIN
DELETE TOP (@BatchSize) 
FROM MyTable
WHERE Date < ‘20160301’';	  
    
     IF @@ROWCOUNT < @BatchSize BREAK

END




 

Read More on deleting large amounts of rows from SQL tables

SQL Server – Optimize delete from table - delete in sql - SQL Server ...

Delete a huge amount of data from a SQL Server table - SQL Server ...


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to delete millions of rows in batches


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer