Share via

Maintenance Plans

  • Article

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';

Note

Adding the TargetServersRole account to the db_dtsoperator role enables the account that the SQL Server Agent runs under on the target server to read all SSIS packages that are stored in the msdb on the master server, including any passwords that are stored inside those packages.

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

Release History

12 December 2006
Changed content:
  • Removed note describing requirement to install SSIS.
  • Corrected the list of fixed server and fixed database roles for which the SQL Server Agent node is visible.
  • Added the section "Additional Configuration for Creating or Managing Maintenance Plans in a Multiserver Environment."
New content:
  • Added content describing functionality added in SQL Server 2005 Service Pack 2:
    • Support for multiserver plans.
    • Capability to group plan tasks into subplans, which can be scheduled to run at different times.
    • Support for logging to remote servers.

17 July 2006
New content:
  • Added information about maintenance plans only executing on databases set to compatibility level 80 or later.

5 December 2005
New content:
  • Added requirement to install SSIS.