{"name":"'TestJob' overran","description":"<p>This checks if a specific job has not finished by the expected time.<\/p>\r\n<ul>\r\n<li>If 0 is returned, the job either: <\/li>\r\n<li>- hasn't started, <\/li>\r\n<li>- is running during the expected time, or <\/li>\r\n<li>- has finished running.<\/li> \r\n<p><\/p>\r\n<li>If 1 is returned, the job is running beyond the expected finish time.<\/li>\r\n<\/ul>\r\n","tsql":"-- Code to examine if a job has overrun to a specific time \r\nDECLARE @todaysdate DATETIME, @job_name NVARCHAR(50), \r\n@expected_start_hour INT, @expected_start_minute INT, \r\n@expected_finish_hour INT, @expected_finish_minute INT \r\n\r\nSELECT @todaysdate = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())), \r\n-- ****************************************************** \r\n-- FILL IN DETAILS SPECIFIC TO YOUR JOB \r\n-- ****************************************************** \r\n@job_name = 'TestJob', \r\n@expected_start_hour = 17, \r\n@expected_start_minute = 0, \r\n@expected_finish_hour = 17, \r\n@expected_finish_minute = 2 \r\n-- Expected outcomes \r\n-- 0=job in expected timescales \r\n-- 1=job has overrun \r\n\r\nSELECT COUNT(*) AS JobOverRunning \r\nFROM msdb..sysjobs j \r\nWHERE \r\nj.name = @job_name \r\nAND EXISTS \r\n( \r\nSELECT TOP 1 * \r\nFROM msdb..sysjobsteps sj \r\nINNER JOIN msdb..sysjobactivity ja ON ja.job_id = j.job_id \r\nWHERE sj.job_id = j.job_id \r\nAND ja.start_execution_date >= DATEADD(mi, @expected_start_minute, DATEADD(hh, @expected_start_hour, @todaysdate)) \r\nAND ja.stop_execution_date IS NULL -- only include stuff that is still running \r\nAND GETDATE() >= DATEADD(mi, @expected_finish_minute, DATEADD(hh, @expected_finish_hour, @todaysdate)) \r\nORDER BY start_execution_date \r\n) \r\n","instances":true,"frequency":60,"databases":{"mode":1},"rateofchange":false,"metricenabled":true,"alertname":"'TestJob' overran","alertdescription":"The job being monitored ran for longer than expected.","aboveorbelow":"above","collections":1,"alertenabled":true,"_thresholds_high":{"selected":false},"_thresholds_medium":{"selected":true,"value":0},"_thresholds_low":{"selected":false},"tsql_comment":"Note: Replace 'TestJob' with the name of the job you're monitoring, and change the start and finish hour and minute accordingly.","name_comment":"Note: Replace 'TestJob' with the name of the job you're monitoring.","alertname_comment":"Note: Replace 'TestJob' with the name of the job you're monitoring.","targetentitytype":0}