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…
Option 1
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.
Option 2
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.
Option 3
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.
- If your maintenance is done by using multiple tools, for example… a Red Gate Backup, a custom re-indexing plan and a simple t-sql script to run a consistency check.
- If your maintenance is done across multiple servers… If you have 3 servers that all backup to the same network share, you could have one server execute at a time to not clog up the network and the storage.
Adding a step to execute the next job is pretty simple.
exec sp_start_job @job_name=N'My Job Name'If you need to schedule this to occur across server, you can simply make the call to the other server using a linked server.
I hope this tip has helped you in one fashion or another. If you would like my list of TOP 10 TIPS FOR SQL SERVER PERFORMANCE AND RESILIENCY can be found here with Tip # 1.