Sql-server – Strange Log Shipping Error Message

log-shippingsql server

Environment and Server Details:

  • 12.0.4457
  • Windows Server 2012 R2 standard
  • VMWare Virtual Machine
  • Dell Equal Logic SAN

SQL Error Log Message:

The log shipping secondary database ServerName.DatabaseName has restore threshold of 45 minutes and is out of sync. No restore was performed for 34089 minutes. Restored latency is 2 minutes. Check agent log and log shipping monitor information.

I've verified that the logs are being restored on the log shipping secondary, matched up the LSNs, also, I have the Log Shipping Monitor from Red Gate that verifies my logs are up to date. Further, the aforementioned error message is for every database in the instance.

I've built a similar setup in a lab environment and I get the same error messages.

  • What gives, anyone have any insight?
  • Can I safely ignore these messages since I know the logs are being
    backed up, copied, and restored?

Having 10s of thousands of error messages per week makes it pretty difficult to peruse and check other possible issues

Best Answer

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)