SQL Server

Nine Undocumented SQL Server 2014 Database Maintenance Plan Stored Procedures

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′