March 25, 2009 at 10:45 am
Hi guys,
If certain job is scheduled to run in certain time then how can we check if that job is running or not on schedule. Suppose a cleanup job is scheduled to run every 20 min but somebody disabled the job so it is not running like say an hour, it should alert me that this particular job is not running for last 1 hour. Can you help me please?
Thanks,
March 25, 2009 at 3:57 pm
Hi,
just played around with your question...
With the script below you can check if a job finished xx minutes ago. You could use this script to set up a another job which checks if your job ran sucessfully.
Hmm..but if someone disables the job which checks the other job, then you maybe need another job checking the checker job... 🙂
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 your job :-)
exec sp_start_job 'Testjob'
END
-----------------------
SQL Server Database Copy Tool at Codeplex
March 25, 2009 at 5:49 pm
Thanks Jetro for the reply. I will try with your code. Actually I have a list of jobs to be checked so I m thinking of putting those jobs in a table and use cursor to check each of them and using your code.
Thanks once again. I will definetly let u know If I can come up with something I wanted.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply