Detecting long running but incomplete SQL jobs

Peter_DN

Hello, is there a way to detect and subsequently alert on long running, but incomplete, jobs?

The challenge linked to this is described in the Comment section of the following article.

https://sqlmonitormetrics.red-gate.com/long-running-job/?_ga=2.170821664.1462435882.1644312637-1865358946.1635173104

The dbo.sysjobhistory table is only updated after the job step has completed. This means we cannot be alerted about a job taking longer than expected until the jobstep is finished. 

Peter

Tom_C

Hi Peter,

The custom metric page you've linked is quite an old one and functionality in the alerting has now progressed.

Please find the following article to the out of the box alerts, both long running query and unusual job duration are continuous and so depending on the threshold set within your system should be adequate for the scenario described for alerting prior to a job/query ending. 
https://documentation.red-gate.com/sm/metrics-alerts-and-notifications/metrics-and-alerts-reference/list-of-alerts#Listofalerts-LongRunningQueryLong-runningquery

Appreciate we dealt with this on another support ticket but didn't want to leave this forum post unanswered.