how to stop the delete job

What am I missing here?

This query works fine but I want it to stop after it deletes table data of batch size of 1000.
I have to stop it manually and if not then it keeps deleting the next batch size of 10k until I stop it. I want it to stop after completing the first batch size.


USE AcctDB0
PRINT CONVERT(varchar(19), GETDATE(), 120) + ' Start.'
DECLARE @BATCHSIZE INT, @WAITFORVAL VARCHAR(8), @ITERATION INT, @TOTALROWS INT, @MAXRUNTIME VARCHAR(8), @BSTOPATMAXTIME BIT, @MSG VARCHAR(500)
DECLARE @ROWCNT INT
SET DEADLOCK_PRIORITY LOW;
SET @BATCHSIZE = 1000
SET @WAITFORVAL = '00:00:10'
SET @MAXRUNTIME = '8:58:00' -- 8AM
SET @BSTOPATMAXTIME = 1 -- ENFORCE 8AM STOP TIME
SET @ITERATION = 0 -- LEAVE THIS
SET @TOTALROWS = 0 -- LEAVE THIS
SET @ROWCNT = 0

WHILE @BATCHSIZE>0
BEGIN
    -- IF @BSTOPATMAXTIME = 1, THEN WE'LL STOP THE WHOLE JOB AT A SET TIME...
    IF CONVERT(VARCHAR(8),GETDATE(),108) >= @MAXRUNTIME AND @BSTOPATMAXTIME=1
    BEGIN
        RETURN
    END

    DELETE TOP(@BATCHSIZE)
    FROM [dbo].[usertracking_v3_PageEngaged]
    WHERE SentAt < dateadd(month, -4, getdate() );

    SET @BATCHSIZE=@@ROWCOUNT
    SET @ITERATION=@ITERATION+1
    SET @TOTALROWS=@TOTALROWS+@BATCHSIZE
    SET @MSG = 'Iteration: ' + CAST(@ITERATION AS VARCHAR) + ' Total deletes:' + CAST(@TOTALROWS AS VARCHAR)
    RAISERROR (@MSG, 0, 1) WITH NOWAIT
    WAITFOR DELAY @WAITFORVAL
      CHECKPOINT
END
PRINT 'TOTAL: ' + STR(@ROWCNT) + ' Records Deleted.'
PRINT CONVERT(varchar(19), GETDATE(), 120) + ' Finished.'
Fay ADBAAsked:
Who is Participating?
 
AndyAinscowFreelance programmer / ConsultantCommented:
Try removing this line:
WHILE @BATCHSIZE>0

That is telling the delete job to keep repeating as long as the condition is true
0
 
Fay ADBAAuthor Commented:
Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.