Extend SQL Log Shipping Restore Threshold Efficiently

Extending the Log Shipping Restore Threshold in SQL Server

Log shipping is a solid disaster recovery (DR) solution in SQL Server, offering automated transaction log backups, copying, and restores to a secondary server. However, sometimes network issues, heavy transaction loads, or maintenance tasks can delay log shipping, causing it to exceed the restore threshold and trigger unnecessary alerts.

If you’re encountering frequent log shipping threshold alerts, extending the restore threshold might be a good option. This blog post walks you through how to do it and the considerations involved.

Understanding the Restore Threshold in Log Shipping

The restore threshold is the maximum time (in minutes) that SQL Server allows before it raises an alert about a delayed log restore. By default, this value is 45 minutes. If a log backup isn’t restored within this window, SQL Server assumes there’s a problem and generates an alert.

While this helps detect issues early, false alarms can occur if your environment has expected delays due to:

Large transaction log backups taking longer to copy and restore.
Network slowdowns delaying the log shipping process.
Overlapping maintenance tasks like index rebuilds or full backups.
High database activity, leading to larger log files and longer restore times.

If these factors are common in your environment, extending the restore threshold can reduce unnecessary alerts.

How to Extend the Log Shipping Restore Threshold

Step 1: Check the Current Restore Threshold

Before making changes, find the current restore threshold using the following query:

SELECT secondary_database, restore_threshold
FROM msdb.dbo.log_shipping_monitor_secondary;

By default, this value is 45 minutes.

Step 2: Update the Restore Threshold

To extend the restore threshold, use the sp_change_log_shipping_secondary_database stored procedure.

For example, to increase the threshold to 90 minutes, run:

EXEC sp_change_log_shipping_secondary_database  
    @secondary_database = 'YourDatabaseName',  
    @restore_threshold = 90;

This updates the log shipping settings and prevents alerts from triggering until 90 minutes have passed without a log restore.

Step 3: Verify the New Threshold

Confirm that the change was applied by running:

SELECT secondary_database, restore_threshold
FROM msdb.dbo.log_shipping_monitor_secondary;

The new threshold should now reflect the updated value.

When Should You Adjust the Restore Threshold?

Increase it if:
✔ Your transaction log backups are large and take longer to process.
✔ You have planned maintenance that occasionally delays log shipping.
✔ Network latency causes intermittent delays in copying log files.

Keep it low if:
❌ You need near real-time failover capabilities.
❌ Any delay in log shipping could indicate a serious issue that needs immediate attention.

Best Practices for Log Shipping Monitoring

Use Alerts and Monitoring – Even with an extended threshold, set up alerts to catch real failures. Use Database Health Monitor (Download Here) for monitoring log shipping health.
Check Copy and Restore Jobs – Make sure the delay isn’t due to SQL Server Agent job failures.
Optimize Backup and Restore Performance – Consider compressing backups to reduce file size and transfer time.
Monitor Disk Space – Ensure there’s enough space on both primary and secondary servers for log file storage.

Need Help?

If log shipping issues are affecting your SQL Server environment, Stedman Solutions can help optimize your database setup, performance, and disaster recovery strategy. Check out our SQL Server Managed Services for expert assistance in keeping your systems running smoothly.

Got questions? Feel free to contact us—we’re here to help! 🚀