Alert when a SQL Job is still running

SQL Server performance monitoring and alerting

Moderators: eddie davis, priyasinha, Adam, chriskelly, Chris Lambrou, Chris Spencer

Alert when a SQL Job is still running

Postby cjvjr75 » Mon May 07, 2012 4:04 pm

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
cjvjr75
 
Posts: 2
Joined: Tue Nov 08, 2011 1:15 pm

Postby Manfred.Castro » Tue May 08, 2012 5:51 pm

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.
Manfred Castro
Product Support
Red Gate Software
Manfred.Castro
 
Posts: 209
Joined: Mon Apr 23, 2012 2:49 pm


Return to SQL Monitor 3

Who is online

Users browsing this forum: No registered users and 0 guests