Handling Multiple Jobs - Revisited

Few days back I wrote about organizing multiple backup jobs so that they will not overlap (to avoid disk and memory contention) and won’t be idle for a long time. Even though my post is about backup jobs, after talking to many people, I decided to genralize the issue
A recap of the scenario here:


We have multiple long time consuming jobs in our servers. Even though they are scheduled to run at different times, often a job can start when another job is still executing, Also there could be an idle time where no job is running. As these jobs take considerable resources, we need to complete all the tasks within the time.

After writing the post,  there were two solutions suggested:

1. Remove the schedules from all your jobs. Manually plan the order of the jobs. Add a step on the first job to call the second job and second job to call the third job and so on. Schedule the first job only
2. Remove the schedules from all your jobs. Manually plan the order of the jobs. Create another Job to call each job and check for the completion of the job and start the next job until all jobs are executed. Schedule this new job.

Each method has its pros and cons.

Method 1:
This is a great method, if you have many jobs of different types. By simply removing the schedule and adding an additional step, (you also need to update the previous job outcome) this could be implemented. Either the code should be cleaver enough in finding the next job using a clear methodology, or else when a job is deleted / disabled, the jobs may start to fail and no other job will execute unless someone change the next job again. Additionally we may need a mechanism of adding the jobs too.

Method 2:

This really works well for backup jobs. If you have many databases and you want to create backups, it is great to have one maintenance job that will create the backups one after the other. In fact, SQL Server’s database maintenance plan does something similar. However, for jobs of multiple categories this is not the best option as different jobs may have different jobs and different notification methods.

Users need to decide on the best solution before making a decision.