Post reply

Script to check whether a job is running or not on schedule

  • March 25, 2009 at 10:45 am

    #202198

    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

    #966339

    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

    #966375

    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