T-SQL chunked delete by id with sleep #tsql

t-sql-chunked-delete-by-id-with-sleep.sql

set nocount on;
set transaction isolation level read uncommitted;

DECLARE @rows_processed INT = 0
DECLARE @row_count INT = 0
DECLARE @max_rows bigint = 1000

-- get bounds
declare @min_id bigint
select @min_id = min(upload_raw_event_id) from raw_data..upload_raw_event (nolock) where event_name = 'update-regex-hit'

DECLARE @max_id bigint
select @max_id = max(upload_raw_event_id) + 1 from raw_data..upload_raw_event (nolock) where event_name = 'update-regex-hit'

if (@min_id is null) begin
	return;
end

declare @start_id bigint = @min_id

declare @msg varchar(1000)

--WHILE (@rows_processed < @max_total_rows_per_run)  
WHILE (@start_id < @max_id)
BEGIN
	WaitFor DELAY '00:00:00:50';

	with X 
		as (select *
			from raw_data..upload_raw_event
			where [upload_raw_event_id] >= @start_id
				and [upload_raw_event_id] < @start_id + @max_rows
				and event_name = 'update-regex-hit'
			)
	delete from X;

	SET @row_count = @@ROWCOUNT

	set @msg = 'rows: ' + cast(@row_count as varchar(20)) + char(13) + char(10)
	RAISERROR(@msg,0,1) WITH NOWAIT  --flush print buffer (but prints nothing after first 500 calls)

	SET @rows_processed = @rows_processed + @row_count

	set @start_id = @start_id + @max_rows
END

select @rows_processed as rows_processed