SQL Server Maintenance Plans History Cleanup Task


By:   |   Updated: 2020-02-18   |   Comments (1)   |   Related: More > 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:

query results

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:

query results

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:

maintenance plan wizard
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:

maintenance plan wizard

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

maintenance plan wizard

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

maintenance plan wizard

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:

maintenance plan wizard

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":

maintenance plan wizard

Finally, we click finish to create the plan:

maintenance plan wizard

Thus, our cleanup task is successfully created:

maintenance plan wizard

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:

excecute maintenance plan

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:

excecute maintenance plan

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:

history cleanup 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
query results

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:



Last Updated: 2020-02-18


get scripts

next tip button



About the author
MSSQLTips author Sergey Gigoyan 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.

View all my tips
Related Resources





Comments For This Article




Monday, July 06, 2020 - 3:09:46 AM - Ronald Maxson Back To Top (86089)

I would recommend one update to the row count queries -- especially for use across a CMS group:

SELECT
(SELECT COUNT(*) FROM msdb.dbo.backupmediafamily) [BMF_COUNT], (SELECT COUNT(*) FROM msdb.dbo.backupset) [BUS_Count], (SELECT COUNT(*) FROM msdb.dbo.restorehistory) [Restore_Count], (SELECT COUNT(*) FROM msdb.dbo.sysjobhistory) [SysJob_Count]

This will return all counts on one line. Running against a CMS group will give you a complete snapshot with one query.



download





Recommended Reading

Reduce Time for SQL Server Index Rebuilds and Update Statistics

Deleting Historical Data from a Large Highly Concurrent SQL Server Database Table

Move SQL Server Maintenance Plan from One Server to Another

Update Statistics for All Tables and Databases in a SQL Server Instance

SQL Server Maintenance Plan Index Rebuild and Reorganize Tasks








get free sql tips
agree to terms


Learn more about SQL Server tools