TSQL Delete Large Number of Rows Without Filling the Transaction Log

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

Comments are closed.