sp_help_maintenance_plan

Transact-SQL Reference

Transact-SQL Reference

sp_help_maintenance_plan

Returns information about the specified maintenance plan. If a plan is not specified, this stored procedure returns information about all maintenance plans.

Syntax

sp_help_maintenance_plan [ [ @plan_id = ] 'plan_id' ]

Arguments

[@plan_id =] 'plan_id'

Specifies the plan ID of the maintenance plan. plan_id is UNIQUEIDENTIFIER. The default is NULL.

Return Code Values

None

Result Sets

If plan_id is specified, sp_help_maintenance_plan will return three tables: Plan, Database, and Job.

Plan Table

Column name Data type Description
plan_id uniqueidentifier Maintenance plan ID.
plan_name sysname Maintenance plan name.
date_created datetime Date the maintenance plan was created.
owner sysname Owner of the maintenance plan.
max_history_rows int Maximum number of rows allotted for recording the history of the maintenance plan in the system table.
remote_history_server int The name of the remote server to which the history report could be written.
max_remote_history_rows int Maximum number of rows allotted in the system table on a remote server to which the history report could be written.
user_defined_1 int Default is NULL.
user_defined_2 nvarchar(100) Default is NULL.
user_defined_3 datetime Default is NULL.
user_defined_4 uniqueidentifier Default is NULL.

Database Table

Column name Description
database_name Name of all databases associated with the maintenance plan. database_name is sysname.

Job Table

Column name Description
job_id ID of all jobs associated with the maintenance plan. job_id is uniqueidentifier.

If no plan ID is specified, or is NULL, sp_help_maintenance_plan will return information about all existing maintenance plans.

Column name Data type Description
plan_id uniqueidentifier Maintenance plan ID.
plan_name sysname Maintenance plan name.
date_created datetime Date the maintenance plan was created.
owner sysname Maintenance plan owner.
max_history_rows int Maximum number of rows allotted for recording the history of the maintenance plan in the system table.
remote_history_server int Name of the remote server to which the history report could be written.
max_remote_history_rows int Maximum number of rows allotted in the system table on a remote server to which the history report could be written.
user_defined_1 int Default is NULL.
user_defined_2 nvarchar(100) Default is NULL.
user_defined_3 datetime Default is NULL.
user_defined_4 uniqueidentifier Default is NULL.

Permissions

Only members of the sysadmin fixed server role can execute sp_help_maintenance_plan.