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