Detecting agent jobs still running but excessivly long
dwalkerw
Posts: 12 Bronze 1
I need a way to detect Agent jobs that are still running, but have exceeded their normal times. I have an occasional maintenance task that runs excessively long and gets into the business day. I need a way to discover these so they can be dealt with before they cause problems. Health Monitor only reports these as excessively long after they end as far as I can tell. Any ideas?
Comments
Thank you for your post.
Have you got the 'Job Duration unusual' and the 'Long Running Query' alerts enabled and set. Could you let us know if they are set, what is the configuration of these alerts.
Once we receive this information we can investigate further.
Many Thanks.
Regards
Wendy Mitchell
Product Support Engineer
What prompted this is appearing to be a problem with Hyper Back, which I understand is now not supported anymore. We were having problems with backups hanging, and tying up backups across all instances of SQL on the same server. I disabled this service last week, and have not had another occurrence since. We will either upgrade Hyper Back to the newer product, or upgrade SQL to 2014 where compression is natively supported.
I looked in the custom metrics and only found this one: http://sqlmonitormetrics.red-gate.com/long-running-job/ I'm guessing you mean the built in 'Job duration unusual' and 'Long-running query' alerts - either way, as long as it works for you that's great!
One thought on the 'Long-running query' alert - in the alert configuration, ensure you have not excluded the backup jobs in the text boxes that match by regex below the alert thresholds. Also ensure that there isn't a more specific level of the hierarchy that is excluding them (you can customise down to the SQL instance level, i.e. all servers > group > server > instance).
If you have not, you may want to check out our product SQL Backup Pro - it effectively does the same thing as Hyperbac but using VDI, though I should note it is a different product and not the continuation of Hyperbac.
Here is a link to download: ftp://support.red-gate.com/patches/SQLBackup/08Jul2014/SQLBackup_7.7.0.18.exe
And here is the documentation landing page with navigation links along the left side: http://documentation.red-gate.com/display/SBU7/SQL+Backup+Pro+7+documentation
There shouldn't be any conflict with the Hyperbac service disabled, and SQL Backup Pro has a free 14 day trial period.
Kind regards,
Alex
Have you visited our Help Center?
Dennis