Undocumented SQL Server 2005 Database Maintenance Plan Stored Procedures
SQL Server 2005 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_update_subplan
In this article, I want to describe these undocumented database maintenance plan stored procedures
shipped with SQL Server 2005.
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 jobs and the maintenance plan's history 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 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.
Examples
This is the example to delete maintenance plans, jobs and history for the 'Sales' database:
USE msdb
EXEC sp_clear_dbmaintplan_by_db @db_name = 'Sales'
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 (is the task was completed successfully or not
and the date/time the task was completed).
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 must be run from 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.
Examples
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 must be run from 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.
Examples
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.
Examples
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'
Arguments
[ @subplan_id = ] 'subplan_id'
The ID of the maintenance subplan. subplan_id is UNIQUEIDENTIFIER, with no default.
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.
Examples
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.
Examples
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.
Examples
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_update_subplan
The sp_maintplan_update_subplan database maintenance plan stored procedure is used to create
a new subplan entry or update an existing one.
Syntax
sp_maintplan_update_subplan
[ @subplan_id = ] 'subplan_id',
[ @plan_id = ] 'plan_id',
[ @name = ] 'name',
[ @description = ] 'description',
[ @job_id = ] 'job_id',
[ @schedule_id = ] 'schedule_id',
[ @allow_create = ] 'allow_create'
Arguments
[ @subplan_id = ] 'subplan_id'
The ID of the maintenance subplan. subplan_id is UNIQUEIDENTIFIER, with no default.
[ @plan_id = ] 'plan_id'
The ID of the maintenance plan. plan_id is UNIQUEIDENTIFIER, with default NULL.
[ @name = ] 'name'
The name of the subplan entry. name is sysname, with default NULL.
[ @description = ] 'description'
The description of the subplan entry. description is NVARCHAR(512), with default NULL.
[ @job_id = ] 'job_id'
The job identifier of the subplan entry. job_id is UNIQUEIDENTIFIER, with default NULL.
[ @schedule_id = ] 'schedule_id'
The schedule identifier of the subplan entry. schedule_id is INT, with default NULL.
[ @allow_create = ] 'allow_create'
The flag indicates does this procedure can create new subplan or not. allow_create is BIT,
with default 0 (this means, that only update is allowed).
Return Code Values
0 (success) or 1 (failure).
Result Sets
None.
Remarks
The sp_maintplan_update_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.
Examples
This is the example to update the description of the maintenance subplan:
USE msdb
EXEC sp_maintplan_update_subplan
@subplan_id = '124FF832-24EC-43CE-8C2D-747374FC3E09',
@description = 'Sales log backup'
