2

We have a 4h datawarehouse job that runs every 4h on 4h schedule. We want to create a new 'daily' schedule and have some processes run out of hours.

However, if when the daily job comes to run and the 4h job is still running, I would like it to wait for the 4h job to complete, then run (or have a specified duration). I haven't decided which is best yet. How is this possible? Thanks!

Please do not suggest 3rd party options as I have no control over the infrastructure.

1

You can right click on the job in SSMS, go to properties, then to the 'schedule' option. From the schedule add your daily schedule to the 4h schedule. Default behavior of SQL Server jobs are that if a job is already running and the same job comes up again, it'll wait for the prior execution to finish before starting a new one. What you're describing is the default behavior in SQL Server, you just need to give the job 2 schedules.

  • you can add a schedule to a schedule that has different packages attached to it? i did not know this was possible... – jhowe Jul 7 '16 at 11:30
  • From what i can see you cannot have steps in a job running on two different schedules. If step 10 is on 4 h schedule you cannot have step 12 running on daily schedule... this doesn't help me sorry. – jhowe Jul 11 '16 at 9:07
0

I would create a main sql agent job that executes the two jobs you mention in your post. You can execute sp_start_job to accomplish this, something like...

Step 1: EXEC sp_start_job 'job A'

Step 2: EXEC sp_start_job 'job B'

Step 2 will not execute until Step 1 is finished.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.