Detecting agent jobs still running but excessivly long

dwalkerw

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?

wendy.mitchell

Hi Dennis

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

dwalkerw

I did find the Job Duration Unusual custom metric after I posted this request. I am working on adjusting the times so the alerts are not throwing off false warnings. This may do the job. The Long Running Queries does not see to pickup the backups.

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.

Alex B

Hi dwalkerw,

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

dwalkerw

We will be upgrading from SQL 2008 to 2014 in the next month or two. We still have not had a reoccurring issue with the backups hanging since Hyperback was disabled. We will consider the need for Backup Pro as part of this upgrade.

Dennis