Batch Delete from Table in MSSQL
This script allows you to perform an operation in batches. This should allow you to keep the log file for the database down whilst performing large operations.
SET ROWCOUNT allows you to set how many records will be effected per query.
SET @intFlag = 1 sets the loop to start at 1
WHILE (@intFlag <=100) loop through this code X times (for this example 100)
SET @intFlag = @intFlag + 1 add 1 to the loop count
SET ROWCOUNT 500000; DECLARE @intFlag INT SET @intFlag = 1 WHILE (@intFlag <=100) BEGIN DELETE FROM tblTemp WHERE tmpDate < '2012-10-01' SET @intFlag = @intFlag + 1 END GO
Here the ROWCOUNT is set to 500,000. On this server attempting to delete 1,000,000 records by running 2 batches of 500,000 delete records was 35 seconds faster than running a single 1,000,000 delete records statement. But running 10 batches of 100,000 delete records was also slower than 2 batches at 500,000 records. This may vary server to server.