Delete Many Rows

Background

To delete many rows can be a heavy operation in sql server. It can take a long time and lock a lot of resources. One trick is to delete in chunks lower then 5000, to awoid lock escalation.

Below is two ways to achive this. The first one is quick and easy, it make use of the repeat in the go command. The second is slightly more complicated but is useful to use when you need to schedule it in the agent.

Quick and easy in ssms


DELETE TOP (4500)
FROM 
	[MyTable]
where 
	Received < (DATEADD(year,-2, GETDATE() ))
    -- If it is possible to do the delete against primary key it's preferable.

GO 50 --Repeats the delete 50 times

To use in agent


DECLARE @Rows INT
DECLARE @Loops INT
SET @Rows = 1
SET @Loops = 0
WHILE @Rows > 0 AND @Loops <= 1000
BEGIN    
	BEGIN TRANSACTION
		DELETE TOP (4000)    
		FROM 
			[MyTable]
		WHERE 
			Received < (DATEADD(year,-2, GETDATE() ))   
		SET @Rows = @@ROWCOUNT    
		SET @Loops = @Loops + 1    
	COMMIT
END
SELECT @Loops