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.