Menu & Search
Batch Deletions in SQL Server

Batch Deletions in SQL Server


This script will delete rows within a table that are out-with the specified retention period. This can be useful if you’re running deletes on large tables, and particularly if;

# Deletes are taking a long time & you’re finding it difficult to track progress.
# You have a short maintenance window each day to perform such tasks, which could be due to caution over blocking/deadlocks.
# Your server is not equipped to handle the log file bloat associated with running the delete as a single transaction.

Whatever the reason is, this is a useful script to have in the toolbelt.

I’ve set this to delete 10k rows at a time, although you can find your own sweet spot.

USE [Adventureworks]
GO

SET NOCOUNT ON 

DECLARE 
  @today_floored DATETIME, 
  @retention_period INT, 
  @retention_date DATETIME 

-- Get today, clearing time to 12:00am 
SET @today_floored = 
  (SELECT DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0)) --+(SELECT [rollover_time] FROM [table]) 
  
-- Set retention period (in months, unless the line above is changed) 
SET @retention_period = 13

-- Apply retention date deduction to floored date 
SELECT @retention_date = 
  DATEADD(m, -@retention_period, @today_floored) 

PRINT 'Deleting data < '+ CAST(@retention_date AS VARCHAR(20)) 

-- Delete in batches; set preferred rowcount and enter the table/column names 
DECLARE 
  @BatchSize INT = 1, 
  @total BIGINT = 0 

SET rowcount 10000 
WHILE @BatchSize <> 0 
BEGIN  
  DELETE [dbo].[FactResellerSalesXL_CCI]
  WHERE [ShipDate] < @retention_date
  
  SET @BatchSize = @@rowcount 
  SET @total = @total + @batchsize 
  PRINT @total 
END

To find out how long it’ll take until all rows have been deleted, you could;
1. Get row counts.
2. Run deletion script for 10 minutes.
3. Check how many rows were affected.
4. Refresh Disk Usage by Top Tables report / get new counts.
5. Compare numbers above.
6. If the math works out to be 9 months before it’ll finish, then get an index added.


Share

1 Comment

  1. […] 5. Perform delete in batches. […]