T-SQL chunked delete by time interval with sleep #tsql

t-sql-chunked-delete-by-time-interval-with-sleep.sql

/*
Need to delete certain rows out of raw_event table.
but raw_event table is partitioned by timestamp and contains a ton of data,
so need to do chunked deletes by small intervals of the partition key (timestamp)
Will try 1 min intervals.
*/

set nocount on;
set transaction isolation level read uncommitted;

DECLARE @rows_processed INT = 0
DECLARE @row_count INT = 0

declare @start_date datetime
select @start_date = '2019-06-17 07:00:00' --min(timestamp) from raw_data..raw_event (nolock)
declare @end_date datetime = '2019-06-19 16:30:00'

declare @current_date datetime = @start_date
declare @next_date datetime = dateadd(minute, 1, @current_date)

declare @msg varchar(1000)

declare @lastHour int = -1

WHILE (@current_date < @end_date)
BEGIN
	WaitFor DELAY '00:00:00:50';

	if ((select datepart(hour, @current_date)) != @lastHour) begin
		set @msg = 'current_date: ' + cast(@current_date as varchar(20)) + char(13) + char(10)
		--RAISERROR(@msg,0,1) WITH NOWAIT;  --flush print buffer on first 500 calls
		print @msg

		set @lastHour = datepart(hour, @current_date);
	end;

	with X 
		as (select *
			from raw_data..raw_event
			where timestamp >= @current_date
				and timestamp < @next_date
				and event_name = 'update-regex-hit' --deletion criteria
			)
	delete from X;

	SET @row_count = @@ROWCOUNT

	if (@row_count > 0) begin
		set @msg = 'rows: ' + cast(@row_count as varchar(20)) + char(13) + char(10)
		--RAISERROR(@msg,0,1) WITH NOWAIT  --flush print buffer on first 500 calls
		print @msg

		SET @rows_processed = @rows_processed + @row_count
	end

	set @current_date = @next_date
	set @next_date = dateadd(minute, 1, @current_date)
END
	
SELECT @rows_processed