Is there a mechanism in SQL Server Agent that can send alert notification if a Job does not complete by a cut-off time?

Question

A job usually completes in an hour. But sometimes, it takes longer or gets stuck. Is there a way to configure a notification in SQL Server Agent if the job does not complete in 1 hour ?

Answer 1

SQL Server agent by default don't have such mechanism..create one more job which starts after one hour from the job starttime which you want monitor and query sys jobs for completed status

Answer 2

The one solution I can at this point by creating a other monitor job and call following query in it for your job, if difference between starttime and stoptime greater than 60 mins then you notify over email.

SELECT 
    j.job_id AS 'JobId',
    name AS 'JobName',
    start_execution_date AS 'StartTime',
    stop_execution_date AS 'StopTime',
    avgruntimeonsucceed,
    DATEDIFF(s,start_execution_date,GETDATE()) AS 'CurrentRunTime',
    CASE WHEN stop_execution_date IS NULL THEN 
            DATEDIFF(ss,start_execution_date,stop_execution_date) ELSE 0 END 'ActualRunTime',
    CASE 
        WHEN stop_execution_date IS NULL THEN 'JobRunning'
        WHEN DATEDIFF(ss,start_execution_date,stop_execution_date) 
            > (AvgRunTimeOnSucceed + AvgRunTimeOnSucceed * .05) THEN 'LongRunning-History'
        ELSE 'NormalRunning-History'
    END 'JobRun',
    CASE 
        WHEN stop_execution_date IS NULL THEN
            CASE WHEN DATEDIFF(ss,start_execution_date,GETDATE())
                        > (AvgRunTimeOnSucceed + AvgRunTimeOnSucceed * .05) THEN 'LongRunning-NOW'
            ELSE 'NormalRunning-NOW'
        END
        ELSE 'JobAlreadyDone'
    END AS 'JobRunning'
 FROM msdb.dbo.sysjobactivity ja
    INNER JOIN msdb.dbo.sysjobs j ON ja.job_id = j.job_id
INNER JOIN (
    SELECT job_id,
    AVG
    ((run_duration/10000 * 3600) + ((run_duration%10000)/100*60) + (run_duration%10000)%100)
    +
    STDEV
    ((run_duration/10000 * 3600) + ((run_duration%10000)/100*60) + (run_duration%10000)%100) AS 'AvgRuntimeOnSucceed'
     FROM msdb.dbo.sysjobhistory
    WHERE step_id = 0 AND run_status = 1
    GROUP BY job_id) art 
    ON j.job_id = art.job_id
WHERE 
(stop_execution_date IS NULL) OR 
    (DATEDIFF(ss,start_execution_date,stop_execution_date) > 60
    AND 
    CAST(LEFT(start_execution_date,11) AS DATETIME) = CAST(LEFT(GETDATE(),11) AS DATETIME))
ORDER BY start_execution_date DESC