sql server backup job timeout

[tram]

We use db maintenance plans for backup os sql server. We would like to
send an alert if the backup job runs for long time(over 3 hours). How
could we achieve this using maintenance plan? Retry time would not help
in this situation. Any ideas?

[Simon Hayes]

I don't believe there's anything built into the maintenance plan
functionality, so you would have to implement something yourself. You
could create a proc which uses sp_help_job and/or the
sysjobs/sysjobsteps table to see how long your jobs have been running
for, then raise an error or send an email. You can schedule your proc
to run every few minutes, or whatever interval is best for your
environment.

Alternatively, if you prefer an external script in Perl/C#/whatever,
the SQLDMO Job object exposes the CurrentRunStatus and LastRunDate
properties (and you can still schedule a script from MSSQL, of course).

Simon