How to delete millions of rows in batches

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 ...

SQL Tutorial for SQL Server