Maintenance Plans

Maintenance plans create a workflow of the tasks required to make sure that your database is optimized, is regularly backed up, and is free of inconsistencies. The Maintenance Plan Wizard also creates core maintenance plans, but creating plans manually gives you much more flexibility. In SQL Server 2005 Database Engine, maintenance plans create an Integration Services package, which is run by a SQL Server Agent job. These maintenance tasks can be run manually or automatically at scheduled intervals.

SQL Server 2005 maintenance plans provide the following features:

• Workflow creation using a variety of typical maintenance tasks. You can also create your own custom Transact-SQL scripts.
• Conceptual hierarchies. Each plan lets you create or edit task workflows. Tasks in each plan can be grouped into subplans, which can be scheduled to run at different times.
• Support for multiserver plans that can be used in master server/target server environments.
• Support for logging plan history to remote servers.
• Support for Windows Authentication and SQL Server Authentication. When possible, use Windows Authentication.

Maintenance plans only run against databases set to compatibility level 80 or higher. The maintenance plan designer in SQL Server Management Studio does not display databases set to compatibility level 70 or lower.

You can migrate SQL Server 2000 database maintenance plans to SQL Server 2005 plans, by right-clicking the SQL Server 2000 database maintenance plan and choosing Migrate. For more information, see How to: Migrate SQL Server 2000 Database Maintenance Plans.

You must be a member of the sysadmin role to create and manage maintenance plans, and to view them in Object Explorer. The SQL Server Agent node in Object Explorer is only displayed for members of the sysadmin fixed server role, SQLAgentReaderRole, SQLAgentUserRole, or SQLAgentOperatorRole fixed database roles.

Additional Configuration for Creating or Managing Maintenance Plans in a Multiserver Environment

If the account that the SQL Server Agent runs under on the target server (TSX) is not a member of the sysadmin fixed server role on the master server (MSX), to import a distributed maintenance plan, the account must be granted the following permission on the master server.

USE msdb;
GO
EXEC sys.sp_addrolemember @rolename = 'db_dtsoperator', @membername = 'TargetServersRole';

If this permission is not granted, the following error will appear in the SQL Server Agent log on the target server when the multiserver SQL Server Agent Job that runs the maintenance plan is refreshed by the target server:

"Failed to import Maintenance Plan <NameOfMaintenancePlan> from < NameOfMasterServer>: Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: While trying to find a folder on SQL an OLE DB error was encountered with error code 0x80040E09 (The EXECUTE permission was denied on the object 'sp_dts_getfolder', database 'msdb', schema 'dbo'.)"

After these permissions are changed, each existing distributed maintenance plan must be reloaded on the target server. Make a small change to each plan and save. This will force the target server to reload the maintenance plan from the master server. By default, the target server will update jobs from the master server every 60 seconds. There will be a minimum delay of 120 seconds after you save the maintenance plan before it appears and can be run on the target server.

For information about how to create a maintenance plan, see How to: Create a Maintenance Plan.

See Also

Tasks

How to: Create Multiserver Maintenance Plans
How to: Add or Modify Maintenance Plan Subplan Schedules

Concepts

Maintenance Plan Wizard

Other Resources

Maintenance Tasks

Help and Information

Getting SQL Server 2005 Assistance

Change History