SQL Server – How to Fix Slow Restore in SQL Log Shipping

Turns out the log shipping configuration has reverted to NORECOVERY instead of STANDBY.

I think the is that there must have been an open session on the database, SSMS or alike and after a set period the log shipping is configured to revert back to NORECOVERY. The original configuration was did not have the 'disconnect users currently connected' option enabled.

To resolve the issue I simply went back into the original database and selected ship transaction logs and then selected the radio button for STANDBY instead of NORECOVERY

This is kind of how log shipping has to work. You need exclusive access to a database to apply a log backup, which means your script has to set the database to single_user or otherwise evict all the users before it can apply the log backup(s).

Some workarounds:

1. Schedule your restores for outside of business hours, and let them continue querying stale data in the meantime.
2. Queue up multiple log backups and apply them, say, every hour or every two hours. Users get periodic updates throughout the day, with interruptions less frequent than the log backup schedule on the primary.
3. Don't force your script to kick users out. This means the log restore will wait and wait and wait until there are no active connections to the database. I've never actually tried this, and its feasibility really depends on how active your secondary is.
4. Have multiple secondaries and cycle through them. I've written pretty extensively about this approach.