SQL Server Maintenance Plan Best Practices

Let me start by saying most of this content is copied directly from Brad's Sure Guide to SQL Server Maintenance Plans. It is an excellent book, I highly recommend reading it if you have the time or referring to it if you have a situation that is not covered here. It is a good reference for particular topics as well.  The purpose of this entry is just to cover the basics that I tend to need every time I setup a Maintenance Plan in MS SQL Server. I hope you find it useful as well. Keep in mind, these suggests assume you have a maintenance window or off peak period when maintenance can be performed. These recommendations may not be good enough for business critical applications that must be available 24/7, but should be good for most other situations. If you don’t know the which options to pick for each task, the Brad’s eBook covers each task in great detail. I have no connection to Brad, but I did find his book very useful.

Maintenance Plan Tasks Overview

Check Database Integrity

The Check Database Integrity task runs DBCC CHECKDB against selected databases and performs an internal consistency check on them to see if there are any problems with their integrity. While this task is very resource intensive, it is critical that you perform it on a regular basis, to ensure that your databases aren't damaged.

Shrink Database

Never use the Shrink Database task. Is that clear enough advice? While we will discuss why it is not a good idea to automatically shrink a database in Chapter 6, the point to keep in mind is that, if you ever need to shrink a database, it should be done manually.

Rebuild Index

The Rebuild Index task runs the ALTER INDEX statement with the REBUILD option on indexes in the selected databases, by physically rebuilding indexes from scratch. This removes index fragmentation and updates statistics at the same time. If you use this option, you do not want to run the Reorganize Index or the Update Statistics task, as doing so would be redundant.

Reorganize Index

The Reorganize Index task runs the ALTER INDEX statement with the REORGANIZE option on the indexes in the selected databases. This task helps to remove index fragmentation, but does not update index and column statistics. If you use this option to remove index fragmentation, then you will also need to run the Update Statistics task as part of the same Maintenance Plan. In addition, you won't need to run the Rebuild Index task, as the use of Reorganize Index task (followed by the Update Statistics task) renders redundant the Rebuild Index task.

Update Statistics

The Update Statistics task runs the sp_updatestats system stored procedure against the tables of the selected databases, updating index and column statistics. It is normally run after the Reorganize Index task is run. Don't run it after running the Rebuild Index task, as the Rebuild Index task performs this same task automatically.

Execute SQL Server Agent Job

The Execute SQL Server Agent Job task allows you to select SQL Server Agent jobs (ones you have previously created), and to execute them as part of a Maintenance Plan. This feature offers you additional flexibility when performing database maintenance using the Maintenance Plan Wizard.

History Cleanup

The History Cleanup task deletes historical data from the msdb database, including historical data regarding backup and restore, SQL Server Agent and Maintenance Plans.
If you don’t perform this task periodically then, over time, the msdb database can grow very large.

Back Up Database (Full)

The Back Up Database (Full) task executes the BACKUP DATABASE statement and creates a full backup of the database. You will probably want to run this task daily against your system and production databases. In most cases, the databases you will be backing up with this task use the Full Recovery model, and you will also want to run the Backup Database (Transaction Log) task as part of your Maintenance Plan.

Back Up Database (Differential)

The Back Up Database (Differential) task executes the BACKUP DATABASE statement using the DIFFERENTIAL option. This task should only be used if you need to create differential backups.

Backup Database (Transaction Log)

The Backup Database (Transaction Log) task executes the BACKUP LOG statement, and, in most cases, should be part of any Maintenance Plan that uses the Back Up Database (Full) task. It is a common practice to run this task every hour or so, depending upon your needs.

Maintenance Cleanup Task

The Maintenance Cleanup task is problematic as it does not really do what it is supposed to do. In theory, it is designed to delete older backup files (BAK and TRN), along with older Maintenance Plan text file reports (TXT) files that you no longer need. The problem is that it can only delete one type of file at a time within a single Maintenance Plan. For example, if you choose to delete older BAK files, it won't delete older TRN or TXT files; if you choose to delete older TRN files, it won't delete older BAK or TXT files.
What we really need is a task that performs all three inside the same Maintenance Plan, but we don't have it. So, what is the best way to delete old BAK, TRN, and TXT files? One way is to use the Maintenance Plan Designer, which allows you to create three separate subplans that will take care of deleting each of these three kinds of files within a single Maintenance Plan (see Chapter 17). However, if you want to use the Maintenance Plan Wizard exclusively to delete all three file types, you must create three different plans to accomplish your goal.

Order to execute tasks

Here are the general guidelines or thoughts as to what should be done in what order.

• Logical Task Ordering. A task such as Clean Up History can be performed at any point in the plan but, for other tasks, there is a certain logical order in which they should be performed.
• It makes sense to start the Maintenance Plan with the Check Database Integrity task, because there is no point in running the rest of the maintenance tasks if the integrity of your database is in question.
• The Back Up Database (Full) should come before the Backup Database (Transaction Log) task as we can't perform a transaction log backup before we perform a full database backup. If we were to try, we would get an error.
• If a Rebuild Index task (or the Reorganize Index and Update Statistics tasks) is performed during the same maintenance window as the Back Up Database (Full) task, then I always perform the Rebuild Index task first. Why? This way, should I need to perform a restore of the backup, it will have its indexes defragmented and will be ready for production.
• The Maintenance Cleanup task, if selected (see previous discussion), should be performed only after the Back Up Database (Full) has been completed. This way, you can ensure that a good backup has been made before deleting any older backups.
• Task Scheduling. If you choose Separate schedules for each task, the scheduling of these tasks (covered later) will determine the actual order in which they occur. For example, it is possible to schedule the tasks in such a way that the Backup Database (Transaction Log) task runs before the first ever Back Up Database (Full) task for a given database, although this would cause the Maintenance Plan to fail. The key thing to remember is that the logical task order you select in Figure 3.7 is not absolute, and that it can be overridden by the schedules you set for each one.
• Some would argue that you should optimize your indexes THEN check the database integrity because sometimes optimizing indexes will correct corruption issues.If you want to go this route, then in the example below you would move the Rebuild Index… line to be first. After doing this, the second step would be check database integrity

Now that we have a basic idea of what the tasks are, we now need to decide what order to execute them. If you were to write this as code this is what it could look like

if (Check Database Integrity succeeds)
{
    Rebuild Index OR (Reorganize Index and Update Statistics)
   
    Back Up Database (Full) or Back Up Database (Differential)
    if (Back up Database (Full) succeeds)
    {
        Backup Database (Transaction Log)
        Maintenance Cleanup Task
    }

    Shrink Database (never)
}

Run these anytime:
    Execute SQL Server Agent Job
    History Cleanup

Controlling order of the tasks

When you start the Maintenance Plan Wizard it will ask you if you want separate schedules for each task or single schedule for the entire plan. This is a big decision because if you choose separate schedules for each task you will get a different job under SQL Agent | Jobs and if you choose a single schedule for all the tasks then you get one job in SQL Agent | Jobs. If you choose single schedule option then the wizard will ask you what order you want them to execute. If you choose separate schedules then you control the order based on the date you schedule the tasks.

Scheduling Consideration

No matter if you choose one or multiple schedules for your tasks you will want to do these operations during off peak hours if at all possible. You can run some tasks in parallel or overlap, while others should not be. For example, the Check Database Integrity task and Rebuild Index task should not be executed at the same time since they are both resource intensive. If you do, you may find your server becoming to slow.

If you choose to have separate schedules for each task then you will need to take an educated guess as to how long each task will take and set the schedule accordingly. The tricky part is that the same task doesn’t always take the same amount of time to run each time. So, you will want to watch it over time to see if it needs to be adjusted to accommodate the longer durations. You can get these durations by going to the View History on the jobs.

How often to run jobs

This will vary depending on your needs, but here are some common guidelines for how often to schedule tasks.

User Databases

• Check Database Integrity – DAILY (or at least WEEKLY)
• Rebuild Index  WEEKLY (DAILY is better). Choose this instead Reorganize Index and Update Statistics if you have enough time
• Reorganize Index and Update Statistics WEEKLY (DAILY is better) Only do this if no time for Rebuild Index
• Back Up Database (Full) WEEKLY
• Back Up Database (Differential) EVERYDAY EXCEPT FULL BACKUP DAY
• Backup Database (Transaction Log) – HOURLY
• Maintenance Cleanup Task – WEEKLY?
• Shrink Database DO NOT SCHEDULE – ON DEMAND ONLY
• Execute SQL Server Agent Job – DEPENDS
• History Cleanup – WEEKLY (or other)

References: Brad's Sure Guid to SQL Server Maintenance Plans (Free eBook)