TSQL Delete Large Number of Rows Without Filling the Transaction Log
TSQL Delete Large Number of Rows Without Filling the Transaction Log
Script to delete in chunks with commit every number of rows:
DECLARE @Count INT
Declare @for_delete INT
Declare @chunk_size INT
SELECT @chunk_size=10000
SELECT @Count = 0
select @for_delete=count(*)from [ION_Data].[dbo].[DataLog2] where TimestampUTC <‘2014-01-01’
While (@Count < @for_delete)
BEGIN
SELECT @Count = @Count + @chunk_size
BEGINTRAN
DELETE top(@chunk_size) FROM [ION_Data].[dbo].[DataLog2] where TimestampUTC <‘2014-01-01’
COMMIT TRAN
Ref Source: http://dbtricks.com/?p=44 5/22/2015