delete table rows from a metadata table

I have this working query but I would like to run/control it from a different database (DBAUtility) under the same server. So, issue these delete statements and control the run under a sql agent job so it wont run directly from the main database, reason for that is that the main database/table have billion of rows.

so create a different table from the results of this query under dbautility database and run the delete statements from there.

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 = 100
SET @WAITFORVAL = '00:00:10'
SET @MAXRUNTIME = '8:14: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.'

Open in new window



Thank you
Fay ADBAAsked:
Who is Participating?
 
Anjana PatilConnect With a Mentor Software DeveloperCommented:
1. Computational cost is very high dateadd(month, -4, getdate() . Assign it to a variable and then use it.
2. Is SentAt column indexed?
3. I would use a indexed column to query and then delete by primary key.

However I never understood your question, but noticed that delete will be forever not because the table is large but mostly due to the high cost of computing dateadd(month, -4, getdate() each time.
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.