December 3, 2020 at 3:07 pm
If Agent Job takes longer than @X minutes, we want to be notified. What is the best way to implement this?
Likes to play Chess
December 3, 2020 at 3:19 pm
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
December 3, 2020 at 6:26 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
December 3, 2020 at 8:43 pm
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.?
Likes to play Chess
December 3, 2020 at 9:44 pm
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;
Likes to play Chess
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply