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


2 views

Recent Posts

See All

Cardinality estimator

Recently I was asked by a software vendor to review a particular query that ran in under a second on a SQL Server 2014 installation at a compatibility level of 110 (SQL Server 2012), but when run unde

Index fragmentation

A law firm client, occasionally has issues with their legal software, that is provided by the global leader in this field. The response from the software provider is always the same - you have index f

Deleting large amounts of data

I had a client call me about wanting to delete a large amount of data from their database. They knew what tables they wanted to delete from. They also knew that deleting large amounts of data causes t