If SQL Agent Job takes longer than @X minutes, get notified.

  • If Agent Job takes longer than @X minutes, we want to be notified.  What is the best way to implement this?

    Voldemar likes to play CHESS (and IS good at it!)

  • Another job, to monitor the job(s), with logic to check sysjobs sysjobhistory etc for running and duration and if over threshold send a mail

  • Have the first step in the job start another job that monitors the starting job.

    For example, if you're trying to monitor JobA, the first step in JobA starts job JobA_monitor to do the checking.

    Personally I'd have a table that "told" the monitoring job the monitoring parameters -- number of minutes to wait, etc..  That way it's easy to change if you want to, say, give JobA more time in the future to finish.

    If you'd like more details on this approach, just let me know.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • and how do you exactly determine that this or that job is still running while already has reached the @X minutes threshold and we want to be notified, and in most cases will let the job complete.?

    Voldemar likes to play CHESS (and IS good at it!)

  • like this:

    -- stop_execution_date is null;

    SELECT

    ja.job_id,

    j.name AS job_name,

    ja.start_execution_date,

    ja.stop_execution_date,

    ISNULL(last_executed_step_id,0)+1 AS current_executed_step_id,

    Js.step_name

    FROM msdb.dbo.sysjobactivity ja

    LEFT JOIN msdb.dbo.sysjobhistory jh

    ON ja.job_history_id = jh.instance_id

    JOIN msdb.dbo.sysjobs j

    ON ja.job_id = j.job_id

    JOIN msdb.dbo.sysjobsteps js

    ON ja.job_id = js.job_id

    AND ISNULL(ja.last_executed_step_id,0)+1 = js.step_id

    WHERE ja.session_id = (SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC)

    AND start_execution_date is not null

    AND stop_execution_date is null;

    Voldemar likes to play CHESS (and IS good at it!)

Viewing 5 posts - 1 through 5 (of 5 total)

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