Technical Article

Check if a sql server agent job ran

,

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

Rate

4 (2)

You rated this post out of 5. Change rating

Share

Share

Rate

4 (2)

You rated this post out of 5. Change rating