Batch Delete with WaitFor and Execution Time

Delete Tables

Today I got a crazy request from an ops dba, "Please create a script that will delete all the records of an specific table in batches of 50k and with a delay of x amount of time and this query can't run not more than 15 minutes at the time."
Eventually I created a store procedure that will take all the requirements as parameters and the ops dba got his happy face because the procedure is flexible enough to take a table name, delay time in seconds and finally an execution time.

Create procedure usp_YOurNameHere_del(@rowcount int = 50000, --#Records per Batch@ExecutionTime int = 15, --#minutes to execute process@dt int = 15, --#Dates target for deletetion@DelayLength char(8) = '00:00:03', --#delay in seconds before next match@delrecords int output --#Records deleted by process)as
Begin/* Variables declaration */declare @older datetime --#Target delete datedeclare @ExecutionDtTime datetime --Execution time declare @cntr int --#While Loop controllerdeclare @today datetime --#Today datetimedeclare
/*Variable Initialization */ @SqlCmd as varchar (max) --SqlScmd set @cntr = @rowcountset @today = getdate()set @older = dateadd (dd,(-1*@dt),@today)set @ExecutionDtTime = dateadd(mi,@ExecutionTime,@today)set
@delrecords = 0 /* set rowCount */Set rowcount @rowcount/* Begin try */begin try--validationsif not exists ( select * from information_schema.tables where table_name = 'YourTableNameHere' and table_schema = 'TableSchema')
16RAISERROR ('Table missing YourTableNameHere', , -- Severity.1 -- State.);
While @cntr = @rowcount and @ExecutionDtTime > @todayBeginBegin Transaction

yourTableNamehere s Delete sfrom With (tablockx, holdlock)inner join (
Id

YourTableNameHere select from with (nolock)
DateColumnHere


Where <= @older)aon a.Id = s.Id Select @cntr=@@rowcount


Commit set @delrecords = @delrecords + @cntrset @today = getdate()

WAITFOR DELAY @DelayLengthendprint 'Records Deleted: '+cast(@delrecords as varchar(8)) end
begin
try catch DECLARE @ErrorMessage NVARCHAR(4000);DECLARE @ErrorSeverity INT;DECLARE @ErrorState INT;



@ErrorMessage if @@trancount > 0 rollback SELECT = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE();-- Use RAISERROR inside the CATCH block to return error-- information about the original error that caused-- execution to jump to the CATCH block.RAISERROR (@ErrorMessage, -- Message text.@ErrorSeverity, -- Severity.@ErrorState -- State.);end
catch end