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.

Scheduling Database Backups: Need a Solution

Recently we had this issue.

We have a huge database server with 2.5 TB of database space. The server has 8 GB of RAM and two dual core processors. It also has a not so good storage device. Due to the limitations in RAM and hard disk, (the disks are really slow) we were facing a problem in taking full backups.

Initially, the database server had a set of jobs, each one was starting on 5th of every month at 2:00 am. It created a whole heap of issues, including too many (is 59 a big number?) backup jobs starting at the same time causing each one blocking the other due to memory crunch and hard disk bottleneck.

So we decided to schedule the task one after the other. We also scheduled it to happen on a weekend of the moth. (example: First Saturday) We did a calculation on hard disk speed and database size and schedule the jobs to start at different times. For the first month everything worked nicely. But when the databases started growing, it forced us to reschedule the backup jobs. Can we reschedule the jobs every month? Currently we have 29 servers to maintain and some have more databases. If we don’t re schedule the jobs we may face one of the two scenarios (actually both scenarios)

1. Before a database backup is completed, the second one starts and creates memory and disk contention.
2. One backup completes earlier than expected but the next backup waits until its time comes.

We also had another issue: most of the databases are inter-connected so the backup jobs need to be completed one after the other. As some of the databases are really huge, the backup jobs pass the 24 hour time frame. It created another issue for scheduling backups.

 How to solve the issue?

Please come up with your solutions.

Share this post :