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.
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'