Keep Your MSDB Clean - Part 2

Updated: May 30, 2021

As part of its regular, ongoing, day-to-day activities, your SQL Server instance would naturally collect historical data about its automated operations. If left unchecked, this historical data could pile up, leading to wasted storage space, performance hits, and even worse issues.

MSDB would obviously be collecting data about the SQL Agent job executions. But there are also a few other types of historical data that needs to be cleaned up once in a while.

In part 1, we covered the following:

  • Output .txt Files

  • CommandLog

  • Backup History

  • Job History

  • Maintenance Plan History

  • SQL Server and SQL Server Agent Error Logs

  • DB Mail History

  • Full-Text Crawl Logs

It turns out that SQL Server has a bunch more stuff that it retains beyond what we covered in the previous blog post. So, now it's time for part 2!

On this page:

Log Shipping History

When monitoring Transaction Log Shipping, SQL Server would store its history in the following tables:

This data is cleaned up using the stored procedure msdb.dbo.sp_cleanup_log_shipping_history. This stored procedure cleans up local and remote copies of the aforementioned tables based on the configured history retention period.

Its default is 7 days, but the retention period can be changed using the following stored procedures per each primary or secondary database: