Scheduling Job Steps

  • I've got this job that needs to be executed in prod during downtime at exactly 9pm EST, starting with a backup. But the database that ill be setting up this job for is large and may take up to 45mins to complete the backup process and kick off execution of the rest of the job, which i need to make sure starts at exactly 9pm EST. So I've got my job steps listed with backup to start at 8:15pm so that it finishes at 9 and the required changes/scripts are made/run

    My question today is... what if this backup that I expect to take 45mins ends up taking 35 and the changes are made to prod before the required time?

    Is there anyway that i can have different schedules for different steps within one job cos i have not been able to figure it out, not even with SSIS, or i should simply just separate the jobs and allow a +/- 15mins gap between them.

    Just my thoughts, i don't even know if this makes any sense

  • michael_okonji (2/15/2016)


    I've got this job that needs to be executed in prod during downtime at exactly 9pm EST, starting with a backup. But the database that ill be setting up this job for is large and may take up to 45mins to complete the backup process and kick off execution of the rest of the job, which i need to make sure starts at exactly 9pm EST. So I've got my job steps listed with backup to start at 8:15pm so that it finishes at 9 and the required changes/scripts are made/run

    My question today is... what if this backup that I expect to take 45mins ends up taking 35 and the changes are made to prod before the required time?

    Is there anyway that i can have different schedules for different steps within one job cos i have not been able to figure it out, not even with SSIS, or i should simply just separate the jobs and allow a +/- 15mins gap between them.

    Just my thoughts, i don't even know if this makes any sense

    What I am hearing you have:

    Step 1 @ 20:15: take backup assuming it takes 45 minutes

    Step 2: run important stuff that you want to run at 21:00

    What you may want:

    New Backup Job starts at 20:15:

    Step 1: take backup

    Existing Job, modified, starts at 21:00:

    Step 1: run important stuff that you want to run at 21:00

    PS: the big question I have is, what if the backup takes longer than 45 minutes? do you still want your changes to start at 21:00 or wait for the backup to complete?

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Well that's my dilemma.. I wouldn't want it to run while my backup hasn't completed but I've decided to go with simple logic and my solution is to split the jobs...

    Check the history of backups of this db and see what the average run time is.. add 5mins to that time and use that as the start time for the second job to make my changes after the backup. My maintenance window is an hour, i was hoping to not give room to any time of inactivity between the backup and running the scripts I have but... I've not been able to find answers to grant my wish yet so... that's all I have

  • How about this:

    Job starts at 20:15:

    Step 1: take backup

    Step 2: T-SQL, if before 21:00 run WAITFOR TIME '21:00'; else just be done

    Step 3: do important stuff

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Awesome Orlando, maybe that might work.. can you pls be more clear on how to incorporate that statement into my code? Much appreciated!

  • Maybe semantics, but wasn't thinking of anything to incorporate in your existing code. I was proposing new code and a new step 2 in your job.

    Suggestion: make a new stored procedure called dbo.the_waiter, like this (pseudocode):

    create proc dbo.the_waiter (@time time)

    as

    begin

    --if current time is before @time

    if( ...... )

    begin

    --wait until @time

    WAITFOR TIME ....;

    end

    end

    go

    [\code]

    In Step 2, make it a Transact-SQL Step Type and call the waiter with a time parameter of 21:00.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Orlando Colamatteo (2/16/2016)


    Maybe semantics, but wasn't thinking of anything to incorporate in your existing code. I was proposing new code and a new step 2 in your job.

    Suggestion: make a new stored procedure called dbo.the_waiter, like this (pseudocode):

    create proc dbo.the_waiter (@time time)

    as

    begin

    --if current time is before @time

    if( ...... )

    begin

    --wait until @time

    WAITFOR TIME ....;

    end

    end

    go

    [\code]

    In Step 2, make it a Transact-SQL Step Type and call the waiter with a time parameter of 21:00.

    Okay.. Thanks!

  • Just have a job step that starts the next job you want to run AFTER the completion of the first job. Easy-peasy-lemon-squeezy! 🙂

    sp_job_start is the sproc IIRC. You could also schedule it for a run if that is needed.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • TheSQLGuru (2/16/2016)


    Just have a job step that starts the next job you want to run AFTER the completion of the first job. Easy-peasy-lemon-squeezy! 🙂

    sp_job_start is the sproc IIRC. You could also schedule it for a run if that is needed.

    As I understand it, it is not that simple. He does not want the second step to start until 9pm even if step 1 finishes at 850.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Orlando Colamatteo (2/16/2016)


    TheSQLGuru (2/16/2016)


    Just have a job step that starts the next job you want to run AFTER the completion of the first job. Easy-peasy-lemon-squeezy! 🙂

    sp_job_start is the sproc IIRC. You could also schedule it for a run if that is needed.

    As I understand it, it is not that simple. He does not want the second step to start until 9pm even if step 1 finishes at 850.

    Right.

    Well what I have resolved to is... after analyzing the last 5 full backups, I got an average execution time of about 25mins, but then I noticed a spike of 40mins in January so I guess this DB I capable of surprising me. SO... I made two jobs, FULLBakup runs from 8:18 and with an extra 2mins allowance should complete by 9pm, that should take care of a 40mins FULLBkup surprise, and then the scripts are set to start at 9:03pm, still allowing an extra mins before scripts start to run.

    I'm gonna leave it to that, let you guys know what happens tomorrow. #FingersCrossed

  • Orlando Colamatteo (2/16/2016)


    TheSQLGuru (2/16/2016)


    Just have a job step that starts the next job you want to run AFTER the completion of the first job. Easy-peasy-lemon-squeezy! 🙂

    sp_job_start is the sproc IIRC. You could also schedule it for a run if that is needed.

    As I understand it, it is not that simple. He does not want the second step to start until 9pm even if step 1 finishes at 850.

    Simple then: just check current time at prior step completion and either start job immediately if after 2100 or schedule it for 2100 start otherwise.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply