SQL Job that takes too long

  • Hi,

    I am trying to send notifications to myself when a articular job that longer than 8 minutes to execute. For that , I have created JOB 2 ( Report Issue Job ) as shown below as a SQL AGENT JOB to send notification. Database configuration was setup and was also able to send myself test email .

    Report Issue Job runs every 30 minutes (5,5:30,6,6:30,..)

    PROD JOB runs every 2 hours (5:21,7:21,9:21,...)

    Both jobs are executing successfully and job 2 is always over 8 minutes but I haven't received any single notification so far. Please help.

     

    USE [msdb]

    IF (

    SELECT DATEDIFF(SECOND, aj.start_execution_date, GETDATE())

    FROM msdb..sysjobactivity aj

    INNER JOIN msdb..sysjobs sj ON sj.job_id = aj.job_id

    WHERE aj.stop_execution_date IS NULL -- condition: job hasn't stopped running

    AND aj.start_execution_date IS NOT NULL AND-- condition: job is currently running

    sj.name = 'PROD SQL JOB' -- TODO: fill in the exact job name to monitor

    AND NOT EXISTS (

    SELECT 1

    FROM msdb.dbo.sysjobactivity new

    WHERE new.job_id = aj.job_id

    AND new.start_execution_date > aj.start_execution_date

    ) -- TODO: change 3600 below to desired treshold value in seconds

    ) >= 300--1200

    EXEC dbo.sp_notify_operator @profile_name = N'MailAlert'

    ,-- TODO

    @name = N'abc@xyz.com'

    ,-- TODO

    @subject = N'Warning: Database refresh job overdue'

    ,-- TODO

    @body = N'WARNING! Daily job to refresh data from PWA on xyz server took longer than expected!. ';-- TODO

    ELSE

    PRINT 'Job not running or not (yet) exceeding treshold';

    GO

  • With >13,000 points, you should know how to format code snippets for presentation to others.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • What happens if you run the SELECT statement on its own?  What number does it return?  What happens if you run from EXEC to the end?  Does it send an e-mail?

    John

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply