Send Mail Alert when Job Hangs

[klewis10367]

I have jobs in SQL 2005 Windows 2003 Server that send me alerts when a job fails. How do I send an alert when a job hangs?

 

[JayKusch]

You could use the following code and have it update a table with the duration currently seen for your jobs that are executing. Then have another job run every minute or so to see if any jobs have been running for more that "x amount" of time and if found ... send an alert/email out with the details.

SELECT DISTINCT  name 
FROM   msdb.dbo.sysjobs j 
WHERE  j.job_id in 
       (SELECT h.job_id 
        FROM   msdb.dbo.sysjobhistory h 
	  WHERE  j.job_id = h.job_id 
	    AND  h.run_status in (4) 
        -- run_duration is Elapsed time in the execution 
        -- of the job or step in HHMMSS format
        -- (200 = 2 minutes):
	    AND run_duration > 200  
	    AND h.job_id in 
              (SELECT TOP 1 h2.job_id 
	       FROM  msdb.dbo.sysjobhistory h2 
	       WHERE h.job_id = h2.job_id
	       ORDER BY run_date, run_time desc )
        )

Legend:
============================
-- Job statuses in sysjobhistory:
-- 0 = Failed
-- 1 = Succeeded
-- 2 = Retry
-- 3 = Canceled
-- 4 = In progress

Thanks

J. Kusch