Sometime back I had responded to a question where the user wanted to know the methods to cleanup the Maintenance Plan history. It is common for the DBA to come across a situation where the Maintenance Plan history grows large and as a result the Log Viewer takes a long time to load. Here are some of the approaches to clean up the Maintenance Plan history.

History Cleanup Task

SQL Server Maintenance Plan GUI has a task named History Cleanup Task. Using this task in the Maintenance Plan will help to clean up the Maintenance Plan History for the duration selected in it.

The History Cleanup Task has an option to selectively delete the history for Backup and Restore, SQL Server Agent Job and Maintenance Plan.

Log File Viewer

This is one of the lesser known options in SSMS. Using the Log File Viewer also the Maintenance Plan History can be cleaned up as shown in the below screenshot.

sp_maintplan_delete_log stored procedure

The Maintenance Plan History can also be cleaned up through T-SQL commands. The undocumented stored procedure sp_maintplan_delete_log can be used for this. The syntax for this stored procedure is as follows.

sp_maintplan_delete_log @plan_id, @subplan_id, @oldest_time

If I want to clear the history which is older than today for all the Maintenance Plans, this script would do the job for me.

EXECUTE msdb..sp_maintplan_delete_log null,null,'2010-03-16T00:00:00'