SQL Server 2008 R2 : Database Maintenance - Managing Maintenance Plans Without the Wizard

5/19/2013 7:26:05 PM
You can create or modify maintenance plans in SQL Server 2008 without using the Maintenance Plan Wizard. To create a new maintenance plan without the wizard, you right-click the Maintenance Plan node in the Object Explorer and select New Maintenance Plan. You are prompted for a maintenance plan name and then taken to the Design tab for the maintenance plan. The Design tab consists of a properties section at the top of the screen and a plan designer surface that is empty for a new maintenance plan.

Existing maintenance plans are displayed in the Design tab when you right-click the plan and select Modify. Figure 1 shows the Design tab for a maintenance plan that was created with the Maintenance Plan Wizard to back up the system databases.

Figure 1. The maintenance plan Design tab.

The Design tab represents a significant difference from the way maintenance plans were managed in SQL Server 2000. The plan designer surface on the Design tab has drag-and-drop capabilities that allow you to add maintenance tasks to your plan. The available tasks are located in the Toolbox component. The Toolbox and the related tasks are shown in Figure 33.13 in the middle portion of the screen. To add a tool from the Toolbox, you drag the item from the Toolbox to the plan designer surface. Alternatively, you can double-click the task, and the task appears on the plan designer surface.

On the plan designer surface, you can move each of the tasks around, link them to other tasks, and edit them by double-clicking them. You can also right-click a task to edit it, group it with other tasks, autosize it, and gain access to other task options. You can right-click an empty section of the plan designer surface to add annotations or comments that provide additional information about the task or the overall plan.


The dialog boxes displayed when you edit a task are unique for each task. The available maintenance plan tasks display an options screen like the one displayed during the execution of the Maintenance Plan Wizard. This provides consistency that is in place regardless of where the task is defined.

Adding a task to an existing maintenance plan is a good starting point to become familiar with the workings of the Design tab. Consider, for example, the maintenance plan shown in Figure 33.13. This plan, which was initially created with the Maintenance Plan Wizard, is used to create full database backups of all the system databases. One critical aspect that is missing from this plan is a task to remove older database backups. The task that can help you with this is the Maintenance Cleanup task. If you double-click that task in the Toolbox, the task is added to the plan designer surface, as shown in Figure 2.

Figure 2. Adding a task to the plan designer surface.

After you add a task to the plan designer surface, you need to configure it. Note that a small red X icon appears on the right side of the task if the task has not yet been configured. To configure the Maintenance Cleanup task, you double-click it on the plan designer surface. Figure 33 shows the screen that appears so you can configure the Maintenance Cleanup task.

Figure 33. Configuring the Maintenance Cleanup task.

You can use the Maintenance Cleanup task to clean up old backup files or maintenance plan text reports. The deletion of older backup files is particularly important because database backups tend to be large files and can use up a significant amount of disk space. The File Location section of the screen enables you to delete a specific file, or you can delete files in a folder based on search criteria. In most cases, you want to search the folder to delete older files.

When cleaning up database backup files, you typically specify the file extension .bak. If you chose to write each database’s backups to a separate folder, you should choose the Include First-Level Subfolders options, which allows you to search all first-level subfolders that exist under the folder specified. This simplifies the cleanup process and eliminates the need to have a separate cleanup task for each subfolder.

In the last section of the configuration screen for the Maintenance Cleanup task, you specify how old a file must be in order to be deleted. The default is four weeks, but you can adjust this setting to the desired time frame by using the related drop-downs. If you uncheck Delete Files Based on the Age of the File at Task Run Time, all files in the folder or subfolders are deleted, regardless of age.


The deletion of database backup files is not based on the file dates or the name of the backup file. The Maintenance Cleanup task uses a procedure named xp_delete_file that examines the database backup and time the backup was created. Renaming the database backup file does not affect its inclusion in the deletion process.

After configuring the options for the Maintenance Cleanup task, you can click the View T-SQL button at the bottom of the screen. This feature reveals what is going on behind the scenes when the plan executes. Prior to SQL Server 2005, you had to obtain this kind of information by using the Profiler.

When you click OK, the task is ready to use in the maintenance plan. The task runs in parallel with the other tasks defined in the plan unless a precedence or link is established between the tasks. To establish a link between the tasks, you select the first task that you want to execute. When the task is selected, a green arrow is shown at the bottom of the task’s box in the plan designer surface. You click the green arrow and drag it to the task that you want to run next. The green arrow is then connected to the other task. If you double-click the green arrow (or right-click and choose Edit), the Precedence Constraint Editor appears, as shown in Figure 4.

Figure 4. The Precedence Constraint Editor.

The paragraph at the top of the Precedence Constraint Editor gives a good description of what a precedence constraint is. In short, it can link tasks together based on the results of their execution. For example, if a backup database task succeeds, a Maintenance Cleanup task can be defined to run next. You can also set the constraint value so that the next task will run only if the first task fails, or you can have the next task run based on the prior task’s completion, regardless of whether if succeeds or fails. In addition, you can link multiple tasks together with precedence. You define the logical relationship between tasks in the Multiple Constraints section of the Precedence Constraint Editor.

  •  SQL Server 2008 R2 : The Maintenance Plan Wizard (part 2)
  •  SQL Server 2008 R2 : The Maintenance Plan Wizard (part 1)
  •  SQL Server 2008 : Common performance problems (part 2)
  •  SQL Server 2008 : Common performance problems (part 1) - Procedure cache bloating
  •  SQL Server 2008 : SQLOS schedulers, Wait analysis
  •  MySQL for Python : Creating Users and Granting Access - Removing privileges in MySQL, Using REVOKE in Python
  •  MySQL for Python : Creating Users and Granting Access - GRANT access in MySQL
  •  MySQL for Python : Creating Users and Granting Access - Removing users in MySQL, DROPping users in Python, Granting access in Python
  •  MySQL for Python : Creating Users and Granting Access - Creating users from Python
  •  MySQL for Python : Creating Users and Granting Access - Creating users in MySQL
    Most View
    Microsoft SharePoint 2010 Web Applications : Presentation Layer Overview - Ribbon (part 1)
    The Cyber-athletic Revolution – E-sports’ Era (Part 1)
    Windows Server 2003 : Implementing Software Restriction Policies (part 4) - Implementing Software Restriction Policies - Creating a Path Rule, Designating File Types
    Sql Server 2012 : Hierarchical Data and the Relational Database - Populating the Hierarchy (part 1)
    Two Is Better Than One - WD My Cloud Mirror
    Programming ASP.NET 3.5 : Data Source-Based Data Binding (part 3) - List Controls
    Windows 8 : Configuring networking (part 5) - Managing network settings - Understanding the dual TCP/IP stack in Windows 8, Configuring name resolution
    Nikon Coolpix A – An Appealing Camera For Sharp Images (Part 2)
    Canon PowerShot SX240 HS - A Powerful Perfection
    LG Intuition Review - Skirts The Line Between Smartphone And Tablet (Part 2)
    Popular Tags
    Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Indesign Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe After Effects Adobe Photoshop Adobe Fireworks Adobe Flash Catalyst Corel Painter X CorelDRAW X5 CorelDraw 10 QuarkXPress 8 windows Phone 7 windows Phone 8 BlackBerry Android Ipad Iphone iOS
    Top 10
    Review : Acer Aspire R13
    Review : Microsoft Lumia 535
    Review : Olympus OM-D E-M5 Mark II
    TomTom Runner + MultiSport Cardio
    Timex Ironman Run Trainer 2.0
    Suunto Ambit3 Peak Sapphire HR
    Polar M400
    Garmin Forerunner 920XT
    Sharepoint 2013 : Content Model and Managed Metadata - Publishing, Un-publishing, and Republishing
    Sharepoint 2013 : Content Model and Managed Metadata - Content Type Hubs