February 15, 2016 at 9:38 pm
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
February 16, 2016 at 12:24 am
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/runMy 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
February 16, 2016 at 12:55 am
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
February 16, 2016 at 2:18 am
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
February 16, 2016 at 4:12 am
Awesome Orlando, maybe that might work.. can you pls be more clear on how to incorporate that statement into my code? Much appreciated!
February 16, 2016 at 6:21 am
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
February 16, 2016 at 6:54 am
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!
February 16, 2016 at 8:19 am
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
February 16, 2016 at 11:05 am
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
February 16, 2016 at 11:26 am
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
February 16, 2016 at 11:35 am
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 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply