|
Home > Archive > microsoft.public.sqlserver.server > August 2002 > Nested SQL jobs fire off in parellel, not sequentially. Any ideas?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Nested SQL jobs fire off in parellel, not sequentially. Any ideas?
|
|
| Tom Roush 2002-08-13, 11:23 am |
| I've got a number of sql jobs that would be best fired off
one right after the other, but because they vary in length
of time from day to day, it's hard to come up with a
schedule that will work effectively.
I've got (for example) 6 jobs, each with no less than 7
steps in them, on multiple machines. The goal here is
consistency in both the jobs and their execution.
I figured I'd just nest the jobs on a master server, and
then distribute them. I set it up on a test box to see
how it would work.
Given that all of the jobs being called actually exist, I
created the 'master job' below
Here's what it looks like:
Job(master job)
Step1 sp_start_job 'Restore DB1'
Step2 sp_start_job 'Restore DB2'
Step3 sp_start_job 'Restore DB3'
Step5 sp_start_job 'Build DB1'
Step6 sp_start_job 'Build DB2'
Step7 sp_start_job 'Build DB3'
All are set to go to the next step on success, quit on
failure.
I started this job, refreshed, and to my surprise, found
the three database restore jobs restoring and the three
build jobs failed (since they depended on the existence of
the three databases).
I would have expected that the jobs would have fired off
sequentially.
Is there any way to do this other than calling
the 'Restore DB2' job as the last step in 'Restore DB1'?
(that does work, but makes for a maintenance headache when
you only want to run one of the jobs and it then fires off
the others)
Any thoughts would be greatly appreciated.
Thanks,
Tom
| |
| Larry Kippenhan 2002-08-13, 11:23 am |
| What you might try is taking the statement from each of the individual jobs,
and place those in each individual step.
For instance, if you have a backup job:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID 7E5D8803-AFAA-46ED-898E-1C6ED3A954F4
-Rpt "c:\data\MSSQL\LOG\DB Backup and Checks Plan4.txt" -DelTxtRpt 4WEEKS
-WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpDB "H:" -CrBkSubDir -BkExt
"BAK"'
You can copy that into step 1, then take the text from the next job and put
that into the next step. Then each one has to complete before the next can run.
Hope this helps.
Larry.
"Tom Roush" <tom.roush@gettyimages.com> wrote:
>I've got a number of sql jobs that would be best fired off
>one right after the other, but because they vary in length
>of time from day to day, it's hard to come up with a
>schedule that will work effectively.
>
>I've got (for example) 6 jobs, each with no less than 7
>steps in them, on multiple machines. The goal here is
>consistency in both the jobs and their execution.
>
>I figured I'd just nest the jobs on a master server, and
>then distribute them. I set it up on a test box to see
>how it would work.
>
>Given that all of the jobs being called actually exist, I
>created the 'master job' below
>
>Here's what it looks like:
>
>Job(master job)
> Step1 sp_start_job 'Restore DB1'
> Step2 sp_start_job 'Restore DB2'
> Step3 sp_start_job 'Restore DB3'
> Step5 sp_start_job 'Build DB1'
> Step6 sp_start_job 'Build DB2'
> Step7 sp_start_job 'Build DB3'
>
>All are set to go to the next step on success, quit on
>failure.
>
>I started this job, refreshed, and to my surprise, found
>the three database restore jobs restoring and the three
>build jobs failed (since they depended on the existence of
>the three databases).
>
>I would have expected that the jobs would have fired off
>sequentially.
>
>Is there any way to do this other than calling
>the 'Restore DB2' job as the last step in 'Restore DB1'?
>(that does work, but makes for a maintenance headache when
>you only want to run one of the jobs and it then fires off
>the others)
>
>Any thoughts would be greatly appreciated.
>
>Thanks,
>
>Tom
| |
| Tom Roush 2002-08-13, 12:23 pm |
| Thanks for the input, problem is each of the jobs has multiple steps
(usually 7, sometimes more, depending on which database we're dealing
with) . During restores like this (into our dev/test environments) we're
shrinking production databases, synching users, dropping users, writing
to log files and updating a 'master' database that tracks all of the
stuff we're doing, so the idea below, while a good one for smaller jobs,
wouldn't work for the situation I'm currently dealing with.
Again, thanks very much for your thoughts.
Tom
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
| |
| Larry Kippenhan 2002-08-13, 12:23 pm |
| O.K., here is an idea.
At the end of each step (including the last), you could do something like this:
declare @AMISTILLWAITING BIT
set @AMISTILLWAITING = 1
while @AMISTILLWAITING = 1
begin
WAITFOR DELAY '00:01:00'
.... execute some logic here to determine if the job is still running, if it
is, then don't change @AMISTILLWAITING, if it isn't then change it to a 0 so it
will break out of the loop ...
end
This way each step will not complete until the job has completed.
Larry
"Tom Roush" <tom.roush@gettyimages.com> wrote:
>I've got a number of sql jobs that would be best fired off
>one right after the other, but because they vary in length
>of time from day to day, it's hard to come up with a
>schedule that will work effectively.
>
>I've got (for example) 6 jobs, each with no less than 7
>steps in them, on multiple machines. The goal here is
>consistency in both the jobs and their execution.
>
>I figured I'd just nest the jobs on a master server, and
>then distribute them. I set it up on a test box to see
>how it would work.
>
>Given that all of the jobs being called actually exist, I
>created the 'master job' below
>
>Here's what it looks like:
>
>Job(master job)
> Step1 sp_start_job 'Restore DB1'
> Step2 sp_start_job 'Restore DB2'
> Step3 sp_start_job 'Restore DB3'
> Step5 sp_start_job 'Build DB1'
> Step6 sp_start_job 'Build DB2'
> Step7 sp_start_job 'Build DB3'
>
>All are set to go to the next step on success, quit on
>failure.
>
>I started this job, refreshed, and to my surprise, found
>the three database restore jobs restoring and the three
>build jobs failed (since they depended on the existence of
>the three databases).
>
>I would have expected that the jobs would have fired off
>sequentially.
>
>Is there any way to do this other than calling
>the 'Restore DB2' job as the last step in 'Restore DB1'?
>(that does work, but makes for a maintenance headache when
>you only want to run one of the jobs and it then fires off
>the others)
>
>Any thoughts would be greatly appreciated.
>
>Thanks,
>
>Tom
| |
| Matthew Bando 2002-08-14, 9:23 am |
| I haven't tried this, but could you create a dts package, and have each job
executed as a separate step using sp_startjob
and use DTS' workflow to serialize the execution?
Just a thought that I hope helps.
Matthew Bando
BandoM@CSCTechnologies.com
"Tom Roush" <tom.roush@gettyimages.com> wrote in message
news:2c6f01c242e8$be9029c0$9ae
62ecf@tkmsftngxa02...
> I've got a number of sql jobs that would be best fired off
> one right after the other, but because they vary in length
> of time from day to day, it's hard to come up with a
> schedule that will work effectively.
>
> I've got (for example) 6 jobs, each with no less than 7
> steps in them, on multiple machines. The goal here is
> consistency in both the jobs and their execution.
>
> I figured I'd just nest the jobs on a master server, and
> then distribute them. I set it up on a test box to see
> how it would work.
>
> Given that all of the jobs being called actually exist, I
> created the 'master job' below
>
> Here's what it looks like:
>
> Job(master job)
> Step1 sp_start_job 'Restore DB1'
> Step2 sp_start_job 'Restore DB2'
> Step3 sp_start_job 'Restore DB3'
> Step5 sp_start_job 'Build DB1'
> Step6 sp_start_job 'Build DB2'
> Step7 sp_start_job 'Build DB3'
>
> All are set to go to the next step on success, quit on
> failure.
>
> I started this job, refreshed, and to my surprise, found
> the three database restore jobs restoring and the three
> build jobs failed (since they depended on the existence of
> the three databases).
>
> I would have expected that the jobs would have fired off
> sequentially.
>
> Is there any way to do this other than calling
> the 'Restore DB2' job as the last step in 'Restore DB1'?
> (that does work, but makes for a maintenance headache when
> you only want to run one of the jobs and it then fires off
> the others)
>
> Any thoughts would be greatly appreciated.
>
> Thanks,
>
> Tom
|
|
|
|
|