Maintenance Plans - SQL Server 2008

A maintenance plan is a workflow of T-SQL tasks that can be scheduled to run or be executed on demand. Built upon the SSIS engine, maintenance plans provide an easy way to create workflow within your plans, to add custom T-SQL tasks in case the user interface doesn’t provide the specific option you need, and to tell the user at design time what the T-SQL will look like.

maintenance plan that was created to first check a database’s integrity and then issue a backup database statement. If the check database integrity task failed, it would notify an Agent Operator. Notice that two different subplans are associated with this maintenance plan: one subplan is for a test database, and the other is for a development database. Although we could have easily placed these tasks within the same subplan, having multiple subplans allows you to create separate workflows and thus have these subplans run on different schedules.

A sample maintenance plan

A sample maintenance plan

Using SQL Server Management Studio, you can create a maintenance plan either via the Maintenance Plan Wizard or through the Maintenance Plan Designer.

You can launch the Maintenance Plan Wizard by selecting Maintenance Plan Wizard from the context menu of the Maintenance Plans node in Object Explorer. To create a new plan using the Maintenance Plan Designer, simply select New Maintenance Plan from this same context menu.

Launching the Maintenance Plan Wizard will take you through a series of dialog boxes that allow you to pick and choose which tasks you would like to include as part of your plan. If you modify an existing plan or create a new plan, the Maintenance Plan Designer will open as a document window inside the Management Studio shell. In addition, the toolbox will become visible and it will, by default, locate itself beneath Object Explorer, as shown in Figure. The toolbox contains the list of all the available tasks in SQL Server 2008.

Maintenance Plan Tasks

Maintenance Plan Tasks

If you accidentally closed the toolbox, or it doesn’t appear, you can open it from the View menu on the toolbar.

The Maintenance Plan Designer document window opens when you either create a new maintenance plan or modify an existing plan. This document window has two sections. The bottom half is the designer, where the actual workflow is defined. The top half is for metadata about the plan and resembles.

The top half of the Maintenance Plan Designer document window

The top half of the Maintenance Plan Designer document window

The first issue you’ll notice with this section of the designer is that the name field is read-only. When you create a new plan, you’ll be asked for a plan name before you even see the designer. If you use the default name, MaintenancePlan, and later decide you want to change it, you’ll need to rename it through the Object Explorer context menu. You can’t change the name of the maintenance plan directly through the designer. However, the Description field below the Name field is editable; it can hold up to 512 characters.

Across the top of the document window are the following buttons (from left to right):

  • Add Subplan: Adds a subplan to the maintenance plan.
  • Subplan Properties: Allows you to modify the subplan name and description, and also lets you modify the subplan schedule.
  • Delete Subplan (X): Deletes the selected subplan.
  • Modify Subplan Schedule: Allows you to modify the selected subplan schedule.
  • Delete Subplan Schedule: Allows you to delete the selected subplan schedule.
  • Manage Connections: Allows you to manage SQL Server connections, as described in the “Managing Maintenance Plan Connections” section.
  • Logging: Allows you to set up maintenance plan logging and reporting, as explained in the "Reporting and Logging Maintenance Plans" section.

When you create a new maintenance plan, one subplan is created for you by default. This subplan has no schedule defined on it.

Scheduling Maintenance Subplans

Although you can always execute your maintenance subplans individually whenever you want, you probably will want to schedule them to be run at some particular point in the day.

To create a schedule, click the Job Schedule icon to the right of the schedule description column. This will launch the Job Schedule dialog box and allow you to define a schedule for the subplan. In the event that you want to remove a schedule and have the plan return to being an on-demand plan only, click the icon to the right of the Job Schedule icon (the one with the red X). This will delete the schedule associated with the subplan.

Managing Maintenance Plan Connections

The Manage Connections button along the top of the Maintenance Plan Designer document window allows you to add, edit, or remove SQL Server connections. Having multiple connections allows a maintenance plan to perform work on multiple servers, with the results of the tasks rolled up in one central location on the server that is running the maintenance plan.

By default, when a user creates a maintenance plan, a single server connection is generated. This connection is to the local SQL Server on which the plan is created. If you want to perform tasks on other instances of SQL Server, you’ll need to add connections.

Each subplan doesn’t need to contain tasks that all connect to the same server; you could have a subplan whereby every task uses a different connection. This design makes it easier to manage and maintain multiple servers. To manage these connections, click the Connections button. This will bring up the dialog box.

The Manage Connections dialog box

The Manage Connections dialog box

In Figure, notice that the Remove button is disabled for MyServer. This is because each maintenance plan requires you to have at lease one connection for the plan itself.

To add a connection to perform tasks on another SQL Server instance, click the Add button, which launches the dialog box in Figure. By default, the connection name will be blank. You’re free to put in any name you would like for this connection. However, we find it easier to enter the actual server name.

New Connection dialog box

New Connection dialog box

Once this connection is added, you can use it in any of the existing tasks as well as any new tasks that you drop on the designer surface.

Reporting and Logging Maintenance Plans

The Logging button along the top of the Maintenance Plan Designer document window, located to the right of the Manage Connections button, brings up the Reporting and Logging dialog box.

Reporting and Logging dialog box

Reporting and Logging dialog box

With SQL Server maintenance plans, you can generate text file reports of the plan, as well as send the report to an e-mail recipient. Note that you’ll need to install Database Mail in order to send the report to an Agent Operator.

In the Logging section of the Reporting and Logging dialog box, you’ll see two check boxes:

  • Log Extended Information: When this item is checked, all the T-SQL that gets submitted to SQL Server will be included in the maintenance plan history. It’s checked by default and is useful for debugging any issues that arise from plan execution. However, depending on the size of the T-SQL script being executed, this could add a lot of data to your msdb database. To make sure your msdb database doesn’t get too full, you can either uncheck this option or add a History Cleanup task that will automatically remove maintenance plan history tasks older than a certain date.
  • Log to Remote Server: In SQL Server 2008, you can log maintenance plan information to a remote server. This option is useful if you have multiple servers that are running maintenance plans and would like to write all the logging information to a central server for easy management.

Defining Maintenance Plan Tasks

The designer surface is where you actually define what tasks are performed in your maintenance plan. To add a task, simply drag the task from the toolbox onto the designer surface.

For purposes of illustration, let’s create a maintenance plan for the AdventureWorks database that first checks the database integrity (for example, whether it will issue a DBCC CHECKDB() against SQL Server), then on success, performs a database backup. In addition, on failure of the check, the database should notify an Agent Operator.

First, drag the Check Database Integrity, Backup Database, and Notify Operator tasks onto the designer surface.

Maintenance Plan Designer designer surface

Maintenance Plan Designer designer surface

One of the first things you may notice is the red X to the right of each of these tasks. This indicates that the task isn’t configured and needs more information in order to execute—which connection to use, which database to perform the action on, and so on. Right-clicking a task brings up a context menu with the options presented in Table.

Context Menu for aMainentance Task

Context Menu for aMainentance Task

To edit a task, select Edit or double-clickthe task.

Check Database Integrity Task dialog box

Check Database Integrity Task dialog box

The task dialog box may contain Connection, Databases, and Tables (or View) items. In the case of Check Database Integrity, the task needs to know only the connection and which database to check. By default, the local connection is selected; however, if you defined multiple connections, you could select one of these connections from the drop-down list or create a new connection by clicking the New button, which would bring up the New Connection dialog box.

The Databases combo box, when dropped, shows a subdialog box that allows you to pick the databases on which to perform the action.

Databases drop-down combo box

Databases drop-down combo box

Once you select the database or databases to perform the action on, the task may enable or disable options in the dialog box, based on whether you’re performing the action on more than one database.

Once the task is configured (that is, a connection is defined and you’ve selected a database and tables or views as appropriate), you may view the T-SQL that will most likely be executed at runtime against the connection. We say “most likely” because some tasks have conditions that need to be met before they are run, For example, in the case of the Shrink Database task, you define how large the database must be before shrinking, but the View T-SQL button will still always show you the T-SQL that would be executed.

Once you configure the task, the red X will disappear, and you can continue editing the other tasks.

One of the most powerful features of a maintenance plan is its ability to create workflow among the tasks. To do this, select a task, click and hold the green arrow at the bottom of the task, and drag this to another task. By default, the arrow is green, indicating On Success. To change this, simply right-click the green line and select Error or Completion for On Error or On Completion, respectively.

Once you’ve defined your workflow and configured the rest of your tasks, you should be able to execute your plan either via Agent, by scheduling it, or on demand, by right-clicking the plan name in Object Explorer and choosing Execute.

Maintenance plans are an important part of a DBA’s daily routine. This new version of maintenance plans combines the power of the SSIS workflow engine with the usefulness of specific maintenance tasks, wrapped in an easy-to-use feature.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

SQL Server 2008 Topics