January 27, 2017
Maintenance in General is a necessity for SQL Server. No different than changing the oil in your car or going to the Doctor for the annual exam. There is going to be times when you are going to need to run maintenance on your server. The tricky part is trying to determine when you should start the maintenance jobs before the busy time. For example, what if you need to backup your database, then re-index your database and follow it up with a consistency check.
The common approach to this problem is to simply determine how long a job executes (often determined by trial and error) and then adjust the start time of each job to give the job enough time to execute, before starting the next job. The problem with this method is you are hoping the first job in the chain completes on time before you start the second job. The common way to avoid this is leaving gaps so one long running job does not step on the next job. However, there are options…
If you are using a maintenance plan you can keep all the tasks that are schedule to run at the same time in the same sub-plan. Sometimes this does not provide the flexibility that individuals want, but it is an effective method.
You can create multiple steps to a single job. If we use the example above where you want to run a backups, than re-index and then DBCC, you can create 3 different steps, this way as soon as one step completes the next step is executed. This method removes the need for guessing when one job would finish and the next job start.
Each task could have its own job, then the last step of each job would start the next job. This will add a lot of flexibility to your maintenance. I like to use this in a couple different kinds of situations.
Adding a step to execute the next job is pretty simple.
If you need to schedule this to occur across server, you can simply make the call to the other server using a linked server.