Quick Scan Report – Excessive Log Shipping History

Too Much Log Shipping History Filling Up MSDB: What to Do

Log shipping is a popular high-availability and disaster recovery solution in SQL Server environments. It involves regularly backing up transaction logs from a primary server, copying those backups to a secondary server, and restoring them to maintain a synchronized copy of the database. However, one issue that often comes up is the excessive accumulation of log shipping history in the msdb database.

If left unmanaged, the log shipping history can fill up the msdb database, causing performance degradation and even hindering the execution of critical SQL Server Agent jobs. In this post, we’ll look at why this happens and how to clean up and prevent excessive log shipping history from overwhelming your SQL Server.

Why Does Log Shipping History Accumulate in MSDB?

SQL Server uses the msdb database to store various operational and management data, including information related to jobs, alerts, and backups. When you set up log shipping, msdb starts storing detailed records of each log shipping event, such as:

  • Backup history on the primary server
  • Copy history on the secondary server
  • Restore history on the secondary server

Over time, this history grows, especially if you have frequent transaction log backups, long retention periods, or multiple log-shipped databases. Eventually, this can cause msdb to become bloated, and if left unchecked, it can slow down your SQL Server.

The Impact of a Full MSDB

Here are some issues you might experience if log shipping history overfills the msdb database:

  • Slower SQL Server Agent Jobs: The larger the msdb, the longer it takes for SQL Server Agent jobs to query it for job history, potentially delaying job execution.
  • Query Performance: Queries against msdb, such as those run by monitoring tools, could slow down as the history tables grow.
  • Maintenance Overhead: Backing up and maintaining msdb becomes more time-consuming due to the size of the database.
  • Risk of Running Out of Space: In extreme cases, the msdb database can grow so large that it fills the allocated disk space, leading to failed SQL Server operations.

How to Clean Up Log Shipping History

Fortunately, SQL Server provides built-in functionality to manage log shipping history and prevent msdb from becoming too bloated. Here’s a step-by-step guide to clean up and manage log shipping history.

1. Use sp_cleanup_log_shipping_history

SQL Server includes a system stored procedure called sp_cleanup_log_shipping_history, which removes old log shipping history records from msdb. By default, this cleanup procedure runs as part of the log shipping maintenance plan, but you can also execute it manually if you need immediate relief.

Here’s how you can run it:

EXEC msdb.dbo.sp_cleanup_log_shipping_history @retention_period = 4320;

In this example, the @retention_period is specified in minutes (4320 minutes equals 3 days). You can adjust this value according to your retention policy needs. This command removes any log shipping history older than the specified retention period.

2. Automate History Cleanup

To prevent this problem from recurring, ensure that history cleanup is part of your log shipping maintenance process. SQL Server automatically schedules this, but you should verify that it’s running properly. You can find and configure this job in SQL Server Management Studio (SSMS) under SQL Server Agent > Jobs. Look for the job named Log Shipping History Cleanup.

Make sure this job is enabled and runs regularly, based on your retention preferences.

3. Shrink the MSDB Database

After cleaning up log shipping history, you might want to shrink the msdb database to reclaim the space. Be cautious with shrinking, as it can lead to fragmentation, but in cases where space is critically low, it may be necessary.

To shrink msdb, use the following SQL command:

USE msdb;
GO
DBCC SHRINKFILE (N'msdbdata' , 10);  -- Shrinks msdb data file to 10MB
DBCC SHRINKFILE (N'msdblog' , 10);   -- Shrinks msdb log file to 10MB
GO

Adjust the file sizes (10MB in this example) based on your needs.

Best Practices to Prevent Log Shipping History from Filling MSDB

1. Set Appropriate Retention Periods

The retention period controls how long log shipping history is stored. If you are holding onto history longer than needed, reduce the retention period to keep msdb lean.

2. Regular Maintenance

Ensure the Log Shipping History Cleanup job is running at appropriate intervals. You can adjust the frequency of this job based on your log shipping activity levels. High-activity systems may need more frequent cleanup to avoid excessive history buildup.

3. Monitor MSDB Size

Keep an eye on the size of msdb to catch any issues early. Regularly check how much space the log shipping history is consuming, and be proactive about managing it.

You can monitor msdb size using tools like Database Health Monitor, which provides an easy way to track the health and size of system databases like msdb. You can download it for free at DatabaseHealth.com.

Conclusion

Log shipping is a great tool for disaster recovery, but if you’re not careful, log shipping history can cause msdb to fill up, leading to performance problems. Regular cleanup of log shipping history, along with proper retention settings, can help prevent these issues.

If managing log shipping history or other SQL Server maintenance tasks is becoming too time-consuming, consider using our SQL Server Managed Services. We provide continuous monitoring, alerting, and expert remediation for issues like msdb growth. Learn more about how Stedman Solutions can help at Stedman Solutions – Managed Services.

And if you have any questions or need assistance with SQL Server, don’t hesitate to contact us. We’re here to help!

By staying on top of your log shipping history and keeping msdb in check, you can maintain a healthy and high-performing SQL Server environment.