2012年2月21日 下午 12:14
I have one 'master' job setup and scheduled, which runs 3 separate jobs in order
Step 1: Integrity Checks
Step 2; Index Optimisations
Step 3: Database Full Backups
I have noticed that Steps 2 will start to run whilst Step 1 is still completing and likewise Step 3 will run whilst Step 2 is still completing. Obviously I do not wish Full backups to run, whilst index optimisations are running and so forth.
Since, it is good practice to run these steps in order and not wishing to have 3 separate schedules (trying to keep these to a minimum for husbandry purposes) I would have thought that this 'Master' job would have ran fine, but it doesn't.
I have also tried using the WAITFOR DELAY but this really is an unknown amount of time, since optimisations may take 5 mins or 2 hours and the time window to carry this out is low 20:00 - 22:30 approx and varies on what server this runs on.
Any suggestions to have one job, to run three separate jobs, in order and ONLY when the previous step has finished completing?
I have 'On Success - Go to Next Step', already applied.
2012年2月21日 下午 06:52解答者
I am slightly confused, you currently have a single job that has a jobstep for each other individual job and when that jobstep completes you call the other jobs using sp_start_job:
Master Job --> Jobstep 1 --> sp_start_job Job1
On success --> Jobstep 2
Master Job --> Jobstep 2 --> sp_start_job Job2
I'm not quite sure I am understanding you correctly.
2012年2月22日 上午 10:43
I think you can create a maintenance plan and keep the tasks in order. Then you can make the tasks start only if the previous task succeeds or completes which way the other tasks will not run before the first one completes.
The different maintenance tasks that you can have here
Regards, Ashwin Menon My Blog - http:\\sqllearnings.wordpress.com
2012年2月22日 上午 11:56
Thats the jist of it, yes.
I found out the reason. It is because I'm calling individual jobs as steps and this causes them to run asynchronously.
The solution is to script the steps I require within the one job, as apposed to calling sp_start_job within each step.
- 已標示為解答 pugs 2012年2月22日 上午 11:57
2012年2月27日 下午 07:20版主
We currently do not have Job chaining functionality in SQL Agent. You could workaround this issue by writing T_SQL jobstep that starts the job and waits for it to complete by checking job completion status.
Please check if the feature you are requesting is same as https://connect.microsoft.com/SQLServer/feedback/details/696129/sql-agent-job-chaining . If you think that you need this feature in future releases. please add your comments and add your vote.
Sethu Srinivasan [MSFT]
- 已提議為解答 Marty Scherr 2012年3月2日 下午 05:53