Nine Undocumented SQL Server 2014 Database Maintenance Plan Stored Procedures
SQL Server 2014 supports the following useful undocumented database maintenance plan stored
procedures:
- sp_clear_dbmaintplan_by_db
- sp_maintplan_close_logentry
- sp_maintplan_delete_log
- sp_maintplan_delete_plan
- sp_maintplan_delete_subplan
- sp_maintplan_open_logentry
- sp_maintplan_start
- sp_maintplan_subplans_by_job
- sp_maintplan_update_subplan_tsx
Note. These stored procedures exist in the msdb database, not in master.
sp_clear_dbmaintplan_by_db
The sp_clear_dbmaintplan_by_db database maintenance plan stored procedure is used to delete
the maintenance plans, the maintenance plan’s history and the maintenance plan’s jobs for
the specified database.
Syntax
sp_clear_dbmaintplan_by_db [ @db_name = ] ‘db_name’
Arguments
[ @db_name = ] ‘db_name’
The name of the database. db_name is sysname, with no default.
Return Code Values
None.
Result Sets
None.
Remarks
The sp_clear_dbmaintplan_by_db stored procedure exists in the msdb database.
Permissions
By default, only members of the sysadmin fixed server role can execute this stored procedure.
The system administrator can grant the execute permission to run this procedure to other users.
Example
This is the example to delete maintenance plans, jobs and history for the ‘Shop’ database:
USE msdb
EXEC sp_clear_dbmaintplan_by_db @db_name = ‘Shop’
sp_maintplan_close_logentry
The sp_maintplan_close_logentry database maintenance plan stored procedure is used to write
to the maintenance plan log the state of the task.
Syntax
sp_maintplan_close_logentry
[ @task_detail_id = ] ‘task_detail_id’ ,
[ @end_time = ] ‘end_time’ ,
[ @succeeded = ] ‘succeeded’
Arguments
[ @task_detail_id = ] ‘task_detail_id’
The task detail identifier. task_detail_id is UNIQUEIDENTIFIER, with no default.
[ @end_time = ] ‘end_time’
The task complete date. end_time is datetime, with default NULL.
[ @succeeded = ] ‘succeeded’
The flag indicates is the task was completed successfully or not. succeeded is TINYINT, with no default.
Return Code Values
0 (success) or 1 (failure).
Result Sets
None.
Remarks
The sp_maintplan_close_logentry stored procedure exists in the msdb database. If you do not
specify the @end_time parameter the sp_maintplan_close_logentry stored procedure will use
the current date as the @end_time.
Permissions
By default, only members of the sysadmin fixed server role can execute this stored procedure.
The system administrator can grant the execute permission to run this procedure to other users.
Example
This is the example to log the task’s state:
USE msdb
EXEC sp_maintplan_close_logentry
@task_detail_id = ‘8C59CDC6-3099-47E4-B429-A32065A6CE25’, @succeeded = 1
sp_maintplan_delete_log
The sp_maintplan_delete_log database maintenance plan stored procedure is used to delete
the maintenance plan log entries.
Syntax
sp_maintplan_delete_log [ @plan_id = ] ‘plan_id’ ,
[ @subplan_id = ] ‘subplan_id’ ,
[ @oldest_time = ] ‘oldest_time’
Arguments
[ @plan_id = ] ‘plan_id’
The ID of the maintenance plan. plan_id is UNIQUEIDENTIFIER, with default NULL.
[ @subplan_id = ] ‘subplan_id’
The ID of the maintenance subplan. subplan_id is UNIQUEIDENTIFIER, with default NULL.
[ @oldest_time = ] ‘oldest_time’
The date all entries must be deleted before. oldest_time is datetime, with default NULL.
Return Code Values
0 (success) or 1 (failure).
Result Sets
None.
Remarks
The sp_maintplan_delete_log stored procedure exists in the msdb database. @plan_id and
@subplan_id must be both NULL or only one exclusively set. If @oldest_time is NULL all
entries will be deleted.
Permissions
By default, only members of the sysadmin fixed server role can execute this stored procedure.
The system administrator can grant the execute permission to run this procedure to other users.
Example
This is the example to delete all the maintenance plan log entries:
USE msdb
EXEC sp_maintplan_delete_log
sp_maintplan_delete_plan
The sp_maintplan_delete_plan database maintenance plan stored procedure is used to delete
the specified maintenance plan and all its subplans.
Syntax
sp_maintplan_delete_plan [ @plan_id = ] ‘plan_id’
Arguments
[ @plan_id = ] ‘plan_id’
The ID of the maintenance plan. plan_id is UNIQUEIDENTIFIER, with no default.
Return Code Values
0 (success) or 1 (failure).
Result Sets
None.
Remarks
The sp_maintplan_delete_plan stored procedure must be run from the msdb database.
Permissions
By default, only members of the sysadmin fixed server role can execute this stored procedure.
The system administrator can grant the execute permission to run this procedure to other users.
Example
This is the example to delete the maintenance plan and all its subplans:
USE msdb
EXEC sp_maintplan_delete_plan @plan_id = ’94A9DFD6-E140-4C96-B33C-B6BB08E73B12′
sp_maintplan_delete_subplan
The sp_maintplan_delete_subplan database maintenance plan stored procedure is used to delete
the specified maintenance subplan and the job associated with this subplan.
Syntax
sp_maintplan_delete_subplan
[ @subplan_id = ] ‘subplan_id’,
[ @delete_jobs = ] ‘delete_jobs’
Arguments
[ @subplan_id = ] ‘subplan_id’
The ID of the maintenance subplan. subplan_id is UNIQUEIDENTIFIER, with no default.
[ @delete_jobs = ] ‘delete_jobs’
Indicates is the job associated with the subplan will be deleted. delete_jobs is BIT,
with default 1.
Return Code Values
0 (success) or 1 (failure).
Result Sets
None.
Remarks
The sp_maintplan_delete_subplan stored procedure must be run from the msdb database.
Permissions
By default, only members of the sysadmin fixed server role can execute this stored procedure.
The system administrator can grant the execute permission to run this procedure to other users.
Example
This is the example to delete the maintenance subplan:
USE msdb
EXEC sp_maintplan_delete_subplan @subplan_id = ‘124FF832-24EC-43CE-8C2D-747374FC3E09’
sp_maintplan_open_logentry
The sp_maintplan_open_logentry database maintenance plan stored procedure is used to add
a new maintenance plan entry (inserts a new record into sysmaintplan_log table).
Syntax
sp_maintplan_open_logentry
[ @plan_id = ] ‘plan_id’,
[ @subplan_id = ] ‘subplan_id’,
[ @start_time = ] ‘start_time’,
[ @task_detail_id = ] ‘task_detail_id’
Arguments
[ @plan_id = ] ‘plan_id’
The ID of the maintenance plan. plan_id is UNIQUEIDENTIFIER, with no default.
[ @subplan_id = ] ‘subplan_id’
The ID of the maintenance subplan. subplan_id is UNIQUEIDENTIFIER, with no default.
[ @start_time = ] ‘start_time’
The task start time. start_time is datetime, with default NULL.
[ @task_detail_id = ] ‘task_detail_id’
The task detail identifier. task_detail_id is UNIQUEIDENTIFIER, with default NULL.
task_detail_id is an OUTPUT parameter.
Return Code Values
0 (success) or 1 (failure).
Result Sets
None.
Remarks
The sp_maintplan_open_logentry stored procedure must be run from the msdb database. If you do not
specify the @start_time parameter the sp_maintplan_open_logentry stored procedure will use the
current date as the @start_time.
Permissions
By default, only members of the sysadmin fixed server role can execute this stored procedure.
The system administrator can grant the execute permission to run this procedure to other users.
Example
This is the example to add a new maintenance plan entry:
USE msdb
EXEC sp_maintplan_open_logentry
@plan_id = ’94A9DFD6-E140-4C96-B33C-B6BB08E73B12′,
@subplan_id = ‘124FF832-24EC-43CE-8C2D-747374FC3E09’
sp_maintplan_start
The sp_maintplan_start database maintenance plan stored procedure is used to start all jobs
associated with the maintenance plan/subplan.
Syntax
sp_maintplan_start [ @plan_id = ] ‘plan_id’, [ @subplan_id = ] ‘subplan_id’
Arguments
[ @plan_id = ] ‘plan_id’
The ID of the maintenance plan. plan_id is UNIQUEIDENTIFIER, with default NULL.
[ @subplan_id = ] ‘subplan_id’
The ID of the maintenance subplan. subplan_id is UNIQUEIDENTIFIER, with default NULL.
Return Code Values
0 (success) or 1 (failure).
Result Sets
None.
Remarks
The sp_maintplan_start stored procedure must be run from the msdb database. Either @plan_id or
@subplan_id must be exclusively set. If you specify the @subplan_id parameter – this stored
procedure simply start the subplan’s job, if you specify the @plan_id parameter – this stored
procedure loops through subplans and start all associated jobs.
Permissions
By default, only members of the sysadmin fixed server role can execute this stored procedure.
The system administrator can grant the execute permission to run this procedure to other users.
Example
This is the example to start all jobs associated with the maintenance plan:
USE msdb
EXEC sp_maintplan_start
@plan_id = ’94A9DFD6-E140-4C96-B33C-B6BB08E73B12′
sp_maintplan_subplans_by_job
The sp_maintplan_subplans_by_job stored procedure is used to return plan and subplan names
and ids from the sysmaintplan_plans and sysmaintplan_subplans tables if the given job_id
is associated with a maintenance plan.
Syntax
sp_maintplan_subplans_by_job [ @job_id = ] ‘job_id’
Arguments
[ @job_id = ] ‘job_id’
The ID of the job. job_id is UNIQUEIDENTIFIER.
Return Code Values
None.
Result Sets
| Column name | Data type | Description |
| name | sysname | Is the plan name |
| id | UNIQUEIDENTIFIER | The ID of the maintenance plan |
| subplan_name | sysname | Is the subplan name |
| subplan_id | UNIQUEIDENTIFIER | The ID of the maintenance subplan |
Remarks
The sp_maintplan_subplans_by_job stored procedure must be run from the msdb database.
Permissions
By default, only members of the sysadmin fixed server role can execute this stored procedure.
The system administrator can grant the execute permission to run this procedure to other users.
Example
This is the example to return plan and subplan names and ids for the given job:
USE msdb
EXEC sp_maintplan_subplans_by_job @job_id = ’94A9DFA6-E140-4C96-B33C-B6BC08E73B15′
sp_maintplan_update_subplan_tsx
The sp_maintplan_update_subplan_tsx stored procedure is called when a maintenance plan subplan
record needs to be created or updated to match a multi-server Agent job that has arrived from
the master server.
Syntax
sp_maintplan_update_subplan_tsx
[ @subplan_id = ] ‘subplan_id’ ,
[ @plan_id = ] ‘plan_id’ ,
[ @name = ] ‘name’ ,
[ @description = ] ‘description’ ,
[ @job_id = ] ‘job_id’
Arguments
[ @subplan_id = ] ‘subplan_id’
The ID of the maintenance subplan. subplan_id is UNIQUEIDENTIFIER.
[ @plan_id = ] ‘plan_id’
The ID of the maintenance plan. plan_id is UNIQUEIDENTIFIER.
[ @name = ] ‘name’
The name of the maintenance plan. name is sysname.
[ @description = ] ‘description’
The description of the maintenance plan. description is NVARCHAR(512).
[ @job_id = ] ‘job_id’
The ID of the job. job_id is UNIQUEIDENTIFIER.
Return Code Values
None.
Result Sets
None.
Remarks
The sp_maintplan_update_subplan_tsx stored procedure must be run from the msdb database.
Permissions
By default, only members of the sysadmin fixed server role can execute this stored procedure.
The system administrator can grant the execute permission to run this procedure to other users.
Example
This is the example to execute the sp_maintplan_update_subplan_tsx stored procedure:
USE msdb
EXEC sp_maintplan_update_subplan_tsx
@subplan_id = ‘124FF832-24EC-43CE-8C2D-747374FC3E09’,
@plan_id = ’94A9DFD6-E140-4C96-B33C-B6BB08E73B12′,
@name = ‘slplan_name’,
@description = ‘maintplan update subplan’,
@job_id = ’94A9DFA6-E140-4C96-B33C-B6BC08E73B15′