Change 'Testjob' to the name of your job you want to check and define an action (write log, send email, etc.) if the job was not successful.
2012-01-05 (first published: 2011-12-09)
2,530 reads
Change 'Testjob' to the name of your job you want to check and define an action (write log, send email, etc.) if the job was not successful.
use msdb declare @rundate datetime; declare @run_time varchar(6); select @rundate = run_date, @run_time = run_time from ( -- select the last succesful run of your job select top 1 cast(run_date as varchar(8)) as run_date, run_time, run_status from sysjobhistory jh inner join sysjobs j on jh.job_id = j.job_id where step_id = 0 and j.name = 'Testjob' -- your job name order by run_date desc, run_time desc ) a -- add leading zero if needed set @run_time = RIGHT(CAST(CAST(@run_time as int) + 1000000 as varchar(7)),6) -- calculate rundatetime set @rundate = DATEADD(second, cast(right(@run_time,2) as int), @rundate) set @rundate = DATEADD(minute, cast(substring(@run_time, 3,2) as int), @rundate) set @rundate = DATEADD(hour, cast(left(@run_time,2) as int), @rundate) -- if last successful run was more than 25 minutes ago do something if DATEDIFF(minute, @rundate, GETDATE()) > 25 BEGIN -- do something (write mail, netsend etc.) -- or start job exec sp_start_job 'Testjob' END