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.