I want to create a report/alert when a particular job/step for a Job Name (Job_id) exceeds a threshold of duration (e.g. Duration > 00.15.00).

I researched msdb and master for a view or stored procedures that I might use as a template for writing the script. I could find no ready tools (reports or dialogue/options to help me.) I am using SQL2005 9.0.2050.

I have had a job hang for > 1 Day, when it usually takes several seconds. No error message was reported. Once, it actually finished; on another occasion, I killed the job, and started it over to success.

Research did not readily point to the problem. So, I'd like to find any job with excessive duration time, and in particular, I want to track this job. It runs every hour/24 hours, and hangs about once a week.

How 'bout this as an option? I copied and modified a script found that might allow me to stop a 'hung' job long after it should have completed.I could add it as a last step to the job with a WAITFOR DELAY. Every time the job runs, it checks for a 'hung' status, and stops it.