This feature in the maintenance plans in SQL Server seems to be either ignored or over used. Either people do not put this step into any maintenance plan causing MSDB database to grow large over time. Or they have this step placed in every maintenance plan not fully understand what code gets executed.
Let us take a look at what goes on behind the scenes with this maintenance task.
When you add this task to your maintenance plan, the following 3 queries are executed:
declare @dt datetime select @dt = cast(N'2012-11-21T14:08:50' as datetime) exec msdb.dbo.sp_delete_backuphistory @dt GO EXEC msdb.dbo.sp_purge_jobhistory @oldest_date='2012-11-21T14:08:50' GO EXECUTE msdb..sp_maintplan_delete_log null,null,'2012-11-21T14:08:50'
Let’s take a look at each of these stored procedure and what the T-SQL statments are executed behind the scenes.
Looking at the code for the stored procedure, you will see it pull a record set for backup_set_id, media_set_id, and restore_history_id all based off of the date being less than the calculated date from your Remove historical data older than setting.
For each record set, the T-SQL code deletes from the given tables (dbo.backupfile, dbo.backupfilegroup, dbo.restorefile, dbo.restorefilegroup, dbo.restorehistory, dbo.backupset, dbo.backupmediafamily, and dbo.backupmediaset) all records retrieved into the record set. It does not distinguish from which backup maintenance plan it is called. All backup information for all databases is removed at the same time.
Let me use a chart to help explain what will happened
Maintenance Plan 1 Maintenance Plan 2
When Maintenance plan 2 executes on the 23rd of January, the history for Maintenance Plan 1 for Jan 1st will be removed, leaving you with only 3 weeks of history instead of 4 for Maintenance Plan 1.
This stored procedure has 3 parameters:
@job_name sysname = NULL, @job_id UNIQUEIDENTIFIER = NULL, @oldest_date DATETIME = NULL
Only the @oldest_date parameter is used by the cleanup task in the maintenance plan. The stored procedure does check to insure the person running the job does have the correct permission (sysadmin or SQLAgentOperatorRole) to delete job history otherwise it does raise an error. But if the person has the correct permissions, the stored procedure issues the following command.
DECLARE @datepart INT DECLARE @datepart INT DECLARE @timepart INT … IF(@oldest_date IS NOT NULL) BEGIN SET @datepart = CONVERT(INT, CONVERT(VARCHAR, @oldest_date, 112)) SET @timepart = (DATEPART(hh, @oldest_date) * 10000) + (DATEPART(mi, @oldest_date) * 100) + (DATEPART(ss, @oldest_date)) END
… IF(@oldest_date IS NOT NULL) BEGIN DELETE FROM msdb.dbo.sysjobhistory WHERE ((run_date < @datepart) OR (run_date <= @datepart AND run_time < @timepart)) END
The T-SQL executed will not distinquish which job is running at the time and instead will delete the history for all jobs greater then 1 month old. This can cause issues when you have jobs that run only on a monthly basis in which you would like to keep the history for 1 year. One other thing to note this stored procedure call will removed information for all jobs, not just maintenance plan jobs.
This stored procedure has the following parameters:
@plan_id UNIQUEIDENTIFIER = NULL, @subplan_id UNIQUEIDENTIFIER = NULL, @oldest_time DATETIME = NULL
Again, only the @oldest_time parameter is used from the Maintenance plan task. The stored procedure does a check to insure if there is value set for @plan_ID there is one set for @subplan_ID as well. If all three parameters are null, then it deletes all information from dbo.sysmaintplan_logdetail and dbo.sysmaintplan_log tables. Otherwise the following code is executed.
DELETE msdb.dbo.sysmaintplan_log WHERE ( task_detail_id in (SELECT task_detail_id FROM msdb.dbo.sysmaintplan_log WHERE ((@plan_id IS NULL) OR (plan_id = @plan_id)) AND ((@subplan_id IS NULL) OR (subplan_id = @subplan_id)) AND ((@oldest_time IS NULL) OR (start_time < @oldest_time))) )
Since only the @oldest_time parameter is passed into the stored procedure, the last line of the where clause will cause all records with start_time less then the @oldest_time to be deleted from the table.
Again, if you are wanting to keep history for a maintenance plan that only executes on a monthly or bi-yearly, or yearly basis, having this step in the maintenance plans will remove the history you wish to keep.
What can you do?
Now that you understand what the maintenance plan task does, what is the correct way to implement the step in your maintenance plans? It really depends on what information you are wishing to keep track of and for how long. If you only want 4 weeks of data on the history of backups, job history, and maintenance plan logs, then having this step in each maintenance plan will not cause any harm. Or you can implement a new maintenance plan with just this task and schedule it to run on a daily basis.
But if you want more granularity over what information is deleted and when, I would suggest creating an execute T-SQL task calling each of the following stored procedures specifying the parameters in each of your maintenance plans to handle the deletion of the history.
EXEC msdb.dbo.sp_maintplan_delete_log @plan_id = @plan_id, @subplan_id = @subplan_id, @oldest_time = @oldest_time EXEC msdb.dbo.sp_purge_jobhistory @job_name = @job_name, @job_id = @job_id, @oldest_date = @oldest_date
And write your own script to delete the backup and restore history.