Clean up Maintenance Plan History

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'

One thought on “Clean up Maintenance Plan History

  1. sudhir

    Hi Pradeep,

    Nice tip. Is there any way to unlog the backup information to sql server logs. In other words, we do not want to log the backup information on to sql server logs.I tired the maintenance plan history clean up task which clean ups backup info in msdb, maintainence plan, sql server agent history.is there any way for sql server logs??

    Reply

Leave a Reply