Resume job 'A' at some step till job 'B' is completed

Question

I have two SQL Agent jobs 'A' and 'B' Job 'A' has 10 Steps and it starts at 5:00 Am as per schedule, job 'B' has 5 Steps and it Starts by using trigger in step 3 of job 'A', job 'B' should be Completed before starting of step 8 in job'A', But sometimes Job 'B' is not completed when step 8 in job'A' starts.

So, my requirement is, i want to add one step before step 8 in that i want to check the status of job'B' when job'B' is completed then step 8 should start else it should not start and it should wait till job'B' completed

How can i achieve this?

Thank you.

Answer 1

You are using jobs incorrectly, the steps from job B should simply be in job A. Use stored procedures to group and reuse common code not jobs.

However it is possible to do this, in which case this is a duplicate of Good way to call multiple SQL Server Agent jobs sequentially from one main job?

Answer 2

Are you allowed to modify the Jobs?

Can you remove Step 8 through 10 in 'JOB A' create a new JOB C.
Insert step 11 in 'JOB B' then call JOB C from the Job you can run 'sp_start_job' http://msdn.microsoft.com/en-us/library/ms186757.aspx

If you are looking for something more robust there's a product called Tidal enterprise Job scheduler from Cisco. It has a lot of features integrates with sql, db2, oracle, windows,unix, etc.

Answer 3

A simple approach would be to split Job A's step 8 (let's call them A8a and A8b). A8a checks B's status from the msdb tables and does a waitfor delay if B has not completed, or stops if B has errored. This gets a bit tricky in restart scenarios, or if history gets purged from the tables before A8a runs.

You can avoid the busy wait by using a flag. This can be a simple one-row, one-column table. This flag is checked and set by both jobs like this: each job reads the flag. If it is set the other job has finished and step A8b can process. If it is not set, set it and end. Job A will need a new step at A7b to do this; job B will do this as its last step. If the flag is set job B can start A8b by using sp_start_job. You'll have to fiddle with A7's statements and "On success" / "on failure" actions to get it to work. You will need a new step at the start of the whole thing (A0) to ensure the flag's correctly set for each run.

Effectively you've split your stream into 4 by doing this: A1-3, A4-7, B* and A8-10. It would be cleaner to set each up as a separate job, have A3 kick off both A4-7 and B* and then do the "is the flag set" thing at the tail of each of these parallel jobs before starting A8-10.

Of course the real solution is to get a decent scheduler. Other posters have listed some 3rd party ones.