Sql-server – Log Shipping Standby mode

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

I can't comment under the question but i can tell you what i did. I had a similar issue like that due to a break in the restore chain. I tried manually editing the monitor tables to "hack" the replaying of restore logs so that the Alert Job won't throw those alerts but i ended up digging a bigger hole for myself.

Note that even though you are seeing the LS_Restore job giving success messages that doesn't mean it is actually doing a Restore. Click on the View History and look for something similar to this: Number of log backup files restored: 0. (Note that this could mean there's no data to restore or there's data to restore but the log file next in line to restore is missing). I was getting a false positive (wrong successful LS_Restore) but the log files restored were always 0 even though it couldn't find the log file needed (you would see a message saying: Can't find previous log shipping file to be restored).

As a test ensure that there's committed activity in the production database then go to the DR and check the view history for LS_Restore. You want to see something like: Number of log backup files copied: 1. If for 6 restores you don't see a change from "0" files restored then i suggest:

1. Restore your Secondary database using a full + differential back up of the production
   OR
   
2. Re-initialize the log shipping process (delete current log shipping setup and start over the process)