In Chapters 10 and 11, we talked a lot about ways in which you can execute both interactive and batch-scheduled database administrative tasks. In this chapter, we will introduce the SQL Server maintenance plan. SQL Server's maintenance plans give you a way to perform an array of database administrative tasks on a defined schedule. There are two ways to create SQL Server maintenance plans:
The first is by using the Maintenance Plan Wizard, which we will walk you through in detail in this chapter.
The second is to use the maintenance plan authoring environment in Management Studio. Figure 12-1 shows that environment.
It is no surprise that the authoring environment has a high degree of similarity to SQL Server Integration Services (SSIS); as a matter of fact, the maintenance plans are executed from SQL Server Agent as SQL Server integration packages, and the tasks in the maintenance plan designer are part of the SSIS Control Flow toolbox. You can use SSIS packages that perform maintenance tasks as well. Figure 12-2 shows the database maintenance tasks that are part of the SSIS Control Flow toolbox.
To create or manage maintenance plans, you must be a member of the sysadmin fixed server role. Management Studio's Object Explorer will display maintenance plans only for users who are members of this role.
A SQL Server maintenance plan allows you to execute the following tasks:
We've covered some of these tasks already in Chapters 10 and 11. Table 12-1 briefly describes the tasks that are specific to maintenance plans that we haven't yet covered in any detail.
Table 12.1. Maintenance Plan Tasks Unique to the Maintenance Plan Environment
As you can see, the tasks in Table 12-1 focus on providing a comprehensive approach to creating a maintenance plan by doing the following:
Including extensibility options by allowing you to execute SQL Server Agent jobs and Transact-SQL statements
Providing cleanup utilities for the maintenance plan environment and SQL Server backup and job history
Integrating operator notification capabilities directly into the plan definition
Keep in mind that not all tasks that we talk about need to be executed as part of your regular maintenance. For example, you should not shrink your database on a scheduled basis, and you should not update statistics unless you have changed the data distribution in a table significantly.
You can create a maintenance plan using the Maintenance Plan Wizard. The following sections walk you through the process. Basically, first you start the wizard. Then you specify some plan and job properties. Next you lay out the tasks to perform. Then you define the order in which to perform those tasks. Lastly, you configure each task, but we'll cover that in the "Configuring Individual Tasks" section.
Ready? Let's begin.
Here's how to invoke the Maintenance Plan Wizard from Management Studio:
Expand the server.
Expand the Management folder.
Right-click Maintenance Plans, and select Maintenance Plan Wizard.
Step 3 launches the wizard, which you can see in Figure 12-3. You can now step through and create a plan customized to meet your maintenance requirements.
The next step in the process is to specify the plan properties, as shown in Figure 12-4. This is where you specify whether each task in the maintenance plan will run as a separate job on a separate schedule (referred to as subplans) or whether all the tasks will run as one job (one subplan) in SQL Server Agent. You also have the option to select a schedule for this maintenance plan job only in the case where you select a single schedule for the entire plan.
Job schedule properties allow you create a schedule for the maintenance plan. See Figure 12-5 for an example of a schedule. This schedule will be used in the SQL Server Agent job definition that will run the maintenance plan according to the schedule you define.
At this point in the Maintenance Plan Wizard, you can select the maintenance tasks that will be included in the maintenance plan. In this example, we've selected most of the tasks so that you can see the configuration settings for each of them, as shown in Figure 12-6.
Specifying task order is very important, especially for tasks where there is a dependency relationship. For example, reorganizing indexes and then shrinking a database in that order can undo the work of the index reorganize since a database shrink does not preserve the fragmentation state of indexes in a database. In addition, as a best practice, I recommend performing a Check Database Integrity task prior to a Database Backup task. Figure 12-7 shows the Select Maintenance Task Order screen, which allows you to move tasks up or down to set a specific order of execution.
After specifying the tasks to perform and their order, your next bit of business is to go through each task in turn and specify whatever properties or other information are needed in order for the task to perform as you intend. We've selected most of the available tasks in this chapter's example plan. The following sections show you how to configure each one of them.
Check Database Integrity allows you to select the databases that you would like to perform a database consistency check on (by using the DBCC CHECKDB command) for the databases that you select. Figure 12-8 and Figure 12-9 show the two steps involved in selecting a set of databases to perform integrity checks on using the Check Database Integrity task.
The Shrink Database task allows you to select the databases that you would like to shrink (by using the DBCC SHRINKDATABASE command), as shown in Figure 12-10. Notice that there is a condition that you can set to control when the shrink is performed even though it is part of the overall maintenance plan. Also note that you have the ability to shrink only a database; if you need to shrink files, you will have to use the Execute T-SQL Statement task.
The Reorganize Index task allows you to select the set of indexes you would like to reorganize indexes on from the database level down to the table or view level, as shown in Figure 12-11. One thing to note here is that you do not have the ability to specify individual indexes with this task. This task generates the ALTER INDEX Transact-SQL command for each of the indexes included in the selected set of objects. If you need to reorganize individual indexes, you will have to use the Execute T-SQL Statement task.
The Rebuild Index task allows you to select the set of indexes you would like to reorganize indexes on from the database level down to the table or view level, as shown in Figure 12-12. One thing to note here is that you do not have the ability to specify individual indexes with this task. This task generates the ALTER INDEX Transact-SQL command for each of the indexes included in the selected set of objects. If you need to rebuild individual indexes, you will have to use the Execute T-SQL Statement task.
The Update Statistics task allows you to select the set of statistics you would like to update from the database level down to the table or view level, as shown in Figure 12-13. One thing to note here is that you do not have the ability to specify individual statistics with this task. However, you do have the ability to specify the type of statistics to update (column or index), as well as the scan type. This task generates the UPDATE STATISTICS Transact-SQL command for each of the statistics included in the selected set of objects. If you need to update individual statistics, you will have to use the Execute T-SQL Statement task.
The History Cleanup task allows you to select the historical data you would like to remove (backup, SQL Server Agent, or maintenance plan) based on age of the log information, as shown in Figure 12-14. This task generates the Transact-SQL commands for each of the statistics included in the selected set of objects.
The Back Up Database (Full) task allows you to perform the three types of backup mentioned earlier (full, differential, and log) for an individual database or set of databases, as shown in Figure 12-15. This task generates the Transact-SQL commands for each of the databases included in the selected set of objects. This task provides a rich set of options; however, if there are specific options that you require that are not selectable in this task (for example, "Perform checksum before writing to media"), then you can use the Execute T-SQL Statement task.
The Maintenance Cleanup task allows you to remove backup files and reports that were created as part of the maintenance plan execution based on the file location and the file age, as shown in Figure 12-16.
The Select Report Options task allows you to select whether you would like a report of maintenance plan actions and how you would like them distributed. See Figure 12-17 for an example of writing a report to a text file.
Next is the verification step in the wizard prior to the actual creation of the maintenance plan that you are defining. The page shown in Figure 12-18 displays all the choices you have made in the wizard. Once you have verified your choices, click Finish to start the maintenance plan build process.
Figure 12-19 shows the progress report that you'll see while SQL Server builds your maintenance plan. (Think of "build" in this case as analogous to "compile.") You will see an action step for each of the major options you have selected. Verify that each step completes successfully, as shown in the figure.
Once you have created your maintenance plans, you can modify them using SQL Server Management Studio. To modify an existing maintenance plan from Management Studio, perform the following steps:
Expand the server.
Expand the Management folder.
Expand the Maintenance Plans folder
Right-click the maintenance plan you would like to update, in this case SampleMaintenancePlan, and select Modify.
Your menu selection launches the maintenance plan authoring environment from which you can make modifications to your maintenance plans. Figure 12-20 shows this environment, which looks very similar to the SQL Server Integration Services authoring environment.
You can create multiple maintenance plans that have different objectives and goals that run on different schedule. Just make sure that the schedules do not overlap or conflict. For example, if you have a weekly full backup and cleanup maintenance plan that runs on Sunday, make sure that the daily differential backup with no cleanup maintenance plan runs only Monday through Saturday.
The following set of tasks and order of those tasks are the ones we recommend using as a starting point. You can then add tasks based on the observed behavior of your database.
Check database integrity.
Back up the database.
Determine the fragmentation levels of indexes you care about.
Reorganize those with low levels of fragmentation.
Rebuild those with high levels of fragmentation.
Update the statistics of those you reorganized in step 4 (rebuilding automatically updates statistics).
Update any statistics not for tables that had large data distribution changes because bulk-type operations.
Maintenance plans represent a powerful feature in SQL Server and contribute greatly to its ease of use. Be sure to take advantage of them for their value in automating the drudgework involved in database administration. Good, well-designed maintenance plans are one of the key stepping-stones to success for any DBA.