Cleanup MSDB history tables

Job details

Name:	Cleanup MSDB history tables
Platform:	Sqlserver
Category:	Maintenance
Premium package:	Maintenance package
Description:	This task deletes entries in the MSDB database history tables which holds statistics of backup/restore, jobs and maintenance plan executions.
Long description:	This task reduces the size of the history tables which holds statistics of backup/restore, jobs and maintenance plan executions by deleting the entries older than the specified date.
Version:	1.5
Default schedule:	35 1 1 *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2000′ & hasengine=‘YES’]/.[engine_edition = ‘Microsoft SQL Server’ & eng_inst_priv = 0]

Parameters

Name	Default value	Description
delete history threshold	90	Number of days to provide information for in the history tables which holds statistics of backup/restore, jobs and maintenance plan executions.
delete backup/restore history	YES	If set to YES the task will run the sp_delete_backuphistory stored procedure to delete old statistics.
delete jobs history	YES	If set to YES the task will run the sp_purge_jobhistory stored procedure to delete old statistics.
delete maintenance plans history	YES	If set to YES the task will run the sp_maintplan_delete_log stored procedure to delete old statistics.
max delete size	60	The maximum number of days with statistics of backup/restore, jobs and maintenance plan executions which will be deleted/removed per execution of this task.
max records count	60000	If the historical table has less number of rows than the following parameter, the procedure will remove all older rows than the “delete history threshold” parameter value, without taking into account the “max delete size” parameter.

Job Summary

Purpose: The purpose of this job is to maintain the MSDB database by managing the size of its history tables, which hold statistics related to backup/restore, jobs, and maintenance plan executions.
Why: This job is crucial for preventing the MSDB database from becoming excessively large, which can degrade performance and increase maintenance overhead. By periodically cleaning up old entries, it ensures that the database remains manageable and performs optimally.
Manual Checking: You can manually check the current statistics of history tables by executing the following SQL command:

select 'backupset' as "History table", min(backup_finish_date) as "Oldest record", count(*) as "Record count" from msdb.dbo.backupset
union all
select 'sysjobhistory', min(convert(varchar, run_date, 112)), count(*) FROM msdb..sysjobhistory
union all
select 'sysmaintplan_log', min(start_time), count(*) from msdb.dbo.sysmaintplan_log

Implementation Details

The primary stored procedure for this job is dbw_cleanup_history_proc, which handles the deletion of old records based on the provided parameters such as age threshold and maximum deletion size.
Data is logged in the dbw_cleanup_history_tab table each time the job runs, which includes details about the number of records deleted and the total currently remaining.
SQL Server versions greater than 2000 with SQL Server as the engine edition are targeted for this job.

Default Configuration and Parameters

The job is set to run by default at 01:35 on the first day of every month.
Default parameters allow customization of how far back to maintain history data, whether to delete histories of backups/restores, jobs, and maintenance plans independently, and constraints on the volume of records managed per execution.

Reporting

Reports generated by the job include detailed statistics on current record counts and oldest records in the MSDB history tables.
A history of job executions, detailing records deleted during each run, is also available as part of the report output.

Dependencies and Setup

Cast functions and conditional checks are used within the procedure to manage varying circumstances such as the presence of very old records or changes in the volume of stored history over time.
Dependencies include the proper registration of required parameters and the maintenance of the dbw_cleanup_history_tab.

Version Handling and Upgrades

The job logic includes handling for various upgrades from previous versions, ensuring parameters are registered correctly and procedures are updated.
All changes aim to enhance job performance and adapt to potentially larger datasets in the MSDB.

Error Handling

Try and catch blocks are implemented to ensure errors during the execution of stored procedures are captured and logged, providing detailed error messages and failpoints.
Upon catching errors, an update task operation is triggered to log the failure details for further investigation or alerting.

Additional Notes

The job supports forced installation, and it does not have an express installation option, indicating that thorough, manual configuration and review might be beneficial before full deployment.
The overall structure and strategy embedded in this dbWatch job reflect best practices in managing system databases and maintaining optimal database performance and reliability.