SQL Agent Job - One Job To Rule Them All - Steps running before previous Step has completed!

Question

 

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.

Thanks

Answer 1

Hello,

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
Etc

I'm not quite sure I am understanding you correctly.

-Sean

Answer 2

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

HTH

---

Regards, Ashwin Menon My Blog - http:\\sqllearnings.wordpress.com

Answer 3

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.

Answer 4

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.

Thanks

Sethu Srinivasan [MSFT]

SQL Server