- Adam Thurgar
Chaining job schedules
If you have a number of jobs that need to run one after another then you could setup individual schedules for each job. The problem here is that you are estimating how long each job will run for and then adding in some slack before you have the next job scheduled. Example Job 1 starts at 11:00pm and you expect it to take 12 minutes, but you schedule the starting of Job 2 till 11:20pm to make sure that Job 1 is finished, but you may have wasted 8 minutes and if you have a limited window to run these jobs then time is critical, plus what happens if Job 1 takes 25 minutes and Job 2 has already started?
The easier way to chain the running of these jobs is to use sp_start_job in Job 1 to start Job 2. This could be as the final step in the entire job. Therefore you only need to have a schedule for Job 1 and no other jobs. You need to be aware of the type of steps to make sure that the step has been completed, not just that the command has been executed. A T-SQL command is the easiest as the command or commands are executed then the next step will start, but the execution of a stored procedure will complete almost immediately and then the next step will start. So you may need to go and change the stored procedure to add in sp_start_job as the last command in the stored procedure. You will need to test your chaining to make sure that nothing is overlapping, but you will eliminate any wasted waiting time.