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.

The solution focuses on utilizing a view in order to simplify the execution plan produced for a batched delete operation. This is achieved by referencing the given table once, rather than twice which in turn reduces the amount of I/O required.

Before:

delete from t1 where a in (select top (10000) a from t1 order by a);

After:

create view v1 as (select top (10000) * from t1 order by a)

delete from v1

You can also use this method for update operations aswell:

update t1

  set b = 'y'

  from (select top (100) b

        from t1

        order by a ) t1

The posts that provided this information referenced a link (http://blogs.msdn.com/sqlcat/archive/2009/05/21/fa...) provided by Kevin Stephenson of MySpace and Lubor Kollar, a member of the SQL Server Customer Advisory Team. This link is now unavailable but the Internet Archive has a copy of this page at http://web.archive.org/web/20100212155407/http://blogs.msdn.com/sqlcat/archive/2009/05/21/fast-ordered-delete.aspx.

When dealing with deleting data from tables which have foreign key relationships - which is basically the case with any properly designed database - we can disable all the constraints, delete all the data and then re-enable constraints.

-- disable all constraints
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
-- delete data in all tables
EXEC sp_MSForEachTable "DELETE FROM ?"
-- enable all constraints
exec sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

More on disabling constraints and triggers here.

if some of the tables have identity columns we may want to reseed them.

EXEC sp_MSforeachtable "DBCC CHECKIDENT ( '?', RESEED, 0)"

Note that the behaviour of RESEED differs between brand new table, and one which had some data inserted previously from BOL:

DBCC CHECKIDENT ('table_name', RESEED, newReseedValue)

The current identity value is set to the newReseedValue. If no rows have been inserted to the table since it was created, the first row inserted after executing DBCC CHECKIDENT will use newReseedValue as the identity. Otherwise, the next row inserted will use newReseedValue + 1. If the value of newReseedValue is less than the maximum value in the identity column, error message 2627 will be generated on subsequent references to the table.