SQL Server logshipping: Extend the LS_Alert window

All we need is an easy explanation of the problem, so here it is.

We have a logshipping setup, where the LS_Restore job on the secondary database is switched off every night for the datawarehouse to grab its data. The restore cycle is set to 15 minutes and if LS_Restore would kick in, the datawarehouse connection would get severed – the interface therefore switches off LS_Restore. When the datawarehouse interface is done, the LS_Restore job is turned back on.

However, this window can be longer than one hour (by just ten minutes or there-abouts), and this then triggers the LS_Alert job to raise errors.

How can we extend the range on LS_Alert to, say, 1,5 hours?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

update the relevant row and column (backup_threshold (in minutes)) in the log shipping config table

https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/log-shipping-monitor-primary-transact-sql?view=sql-server-2017

Method 2

Although @Mo64’s answer pointed me in the right direction, the issue is with the alert threshold of the secondary server, not the copy speed on the primary. The link provided by Mo64 did also link to the description of the secondary metadata table in msdb. I used those docs to concoct this update statement:

update msdb.dbo.log_shipping_monitor_secondary
set restore_threshold = 120         -- this was 45
where secondary_server = '<your server name here>'

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply