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.
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.
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.
