SQL Server Maintenance Plans History Cleanup Task

By: Sergey Gigoyan | Comments (1) | Related: > Maintenance

Problem

When working with SQL Server maintenance plans, we need to know that having scheduled maintenance plans generates data that gets stored in system tables. A part of this data becomes obsolete over time and should be removed in order to free up space. To make things worse, if we do not take care of cleaning up unnecessary data that gets generated from when scheduled maintenance plans run, the data can become large and fill up all available space causing additional problems. Thus, cleanup tasks should be a part of your maintenance plans.

Solution

This article will discuss how to configure maintenance cleanup tasks to avoid outdated data generated by the maintenance plans. There are two cleanup tasks among the maintenance plans – the History Cleanup Task and the Maintenance Cleanup Task. We will discuss the History Cleanup Task in this article and the Maintenance Cleanup Task in a future article.

SQL Server Management History Data

Historical data about backup and restore jobs, other SQL Server Agent Jobs, and Maintenance Plans are recorded in the msdb system database. This data can be useful in case of troubleshooting Maintenance Plans and SQL Server Agent Jobs. However, over time this data becomes outdated and useless, so it is important to clean the corresponding information from the msdb database.

The History Cleanup Task is especially aimed at removing old data from the msdb database. For example, msdb.dbo.backupmediafamily, msdb.dbo.backupset, msdb.dbo.restorehistory, msdb.dbo.sysjobhistorytables store information about backup, restore, and job activity correspondingly. The queries below retrieve the all data from the above-mentioned tables:

SELECT * FROM msdb.dbo.backupmediafamily
SELECT * FROM msdb.dbo.backupset 
SELECT * FROM msdb.dbo.restorehistory
SELECT * FROM msdb.dbo.sysjobhistory

As we can see, there are many historical records:

The query below shows the row counts of these tables:

SELECT COUNT(*) FROM msdb.dbo.backupmediafamily
SELECT COUNT(*) FROM msdb.dbo.backupset 
SELECT COUNT(*) FROM msdb.dbo.restorehistory
SELECT COUNT(*) FROM msdb.dbo.sysjobhistory

As we are demonstrating this on a test environment, the row counts are quite small. We have counted the rows to compare these values after cleaning up the history data:

SQL Server Maintenance Plan History Cleanup Task

Let’s move on to configuring the history cleanup task.

To illustrate the configuration of this task we will create a sample history cleanup task. It can be created via the Maintenance Plan Wizard and the Maintenance Plan Designer. Let’s use the Wizard to design it.

To launch the Wizard, follow these steps in SSMS:

Go to Management > Maintenance Plans and right-click and select Maintenance Plan Wizard:

Then we click "Next" to move forward to the next step, where we choose a name, description, and schedule for our cleanup task:

After that, we obviously choose the "Clean Up History" task and click "Next":

As we have only one task, we do not need to arrange the order and just click "Next":

In the next window, we set the configurations of the task. As we can see, we can choose which kind of historical data we need to clean Backup and restore history, SQL Server Agent job history and Maintenance plan history. Additionally, we can set how much data to keep:

We will leave the default settings, which means that we are going to clean all mentioned types of historical data records from msdb which are older than four weeks. Then, we click "Next":

Finally, we click finish to create the plan:

Thus, our cleanup task is successfully created:

If we refresh Maintenance Plans and SQL Server Agent Jobs, we can locate the newly created plan and the corresponding job and execute it to test:

The execution is successful which means that all SQL Server Agent jobs history, Maintenance Plans history, and backup and restore history older than 4 weeks is removed from the msdb database:

Now, it is time to review the data in the above-mentioned history tables again. As it is supposed that these experiments are being performed in the test environment, it is supposed that there cannot be old historical data. Therefore, if there is no historical data older than a month, the configuration of the task can be changed from the Maintenance Plan Designer and the value of the "Remove historical data older that:" can be set, for example, 1 hour before the execution of the task:

If we run the below queries once more, we can see the difference. Particularly, in terms of row counts, we can see that the row counts of the tables were reduced (in our example, it is more visible in case of the msdb.dbo.sysjobhistory table as it contained a lot more older records):

SELECT COUNT(*) FROM msdb.dbo.backupmediafamily
SELECT COUNT(*) FROM msdb.dbo.backupset 
SELECT COUNT(*) FROM msdb.dbo.restorehistory
SELECT COUNT(*) FROM msdb.dbo.sysjobhistory

Conclusion

In conclusion, cleaning the unnecessary information generated by the maintenance plans is quite important. As we can see, the History Cleanup Task is aimed at solving such problems and used to remove old data from the msdb database.

Next Steps

For more information, the links below can be useful:

About the author

Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips