Alert when a SQL Job is still running

cjvjr75

Hi,

Last week we got discoverd that one of our SQL jobs was still running.
Nomally the job takes 5 minutes to run, but this time is was still running after 5 days.
The problem was due to the fact that the program that the SQL job was executing was "hung up" and did not respond.
The SQL job was however waiting for a reply.

Now, the server on which the SQL job was active is in SQL Monitor 3 and had the SQL Job Unusual and SQL job failed alerts activated.
However w didn't get an alert. Now since the job didn't report failure i can imagine the SQL Job failure doesn't do anything.
I did expect it from the SQL Job Unusual, since the time running was far higher than the normal time.
It didn't.

Can someone explain this?
Also, is there an option to create a custom metric for this?
Or perhaps another way to achive this?

greetings,

Kick Vieleers

Manfred.Castro

Did the job contain a history of atleat 10 runs?
SQL Monitor will not raise the Job duration unusual alert until the job history contains at least ten runs.
See the notes section for job duration unusual.
http://www.red-gate.com/supportcenter/Content?p=SQL%20Monitor&c=SQL_Monitor/help/3.0/SM_AlertsList.htm&toc=SQL_Monitor/help/3.0/toc1469820.htm
Also since the job never actually finished SQL Monitor would be unable to determine the percentage difference from the baseline because the duration of the job was ongoing.
I suppose the long running query alert may be able to catch this instance but there is no way to know for sure without knowing exactly what the job is doing.