0

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

Improve this question
6
  • did you check for any orphaned entries in logshipping tables e.g. logshipping was setup and then removed without a proper clean up.
    – Kin Shah
    Aug 10, 2016 at 20:51
  • So, I'll have to run those 3 commands for each database on the primary and secondary, correct?
    – AKDiscer
    Aug 10, 2016 at 20:59
  • yes, but just do a select before running the delete so you can confirm if the logshipping tables are having orphaned entries - which I believe is the reason for the error message in the log.
    – Kin Shah
    Aug 10, 2016 at 21:01
  • How do I tell if there are orphaned entries? The result set from the SELECT statement shows legit data. Is there a particular column that I need to look at that'll tell me different?
    – AKDiscer
    Aug 10, 2016 at 21:11
  • try - on primary - select primary_database from log_shipping_monitor_primary where primary_database not in (select name from sys.databases where database_id > 4) same way on secondary server.
    – Kin Shah
    Aug 10, 2016 at 21:13

1 Answer 1

Reset to default
0

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)
Improve this answer
6
  • I've tried both these suggestions and I'm having the same issue.
    – AKDiscer
    Aug 24, 2016 at 21:56
  • That's weird because part 2 is the normal setup of Log Shipping. I noticed some people say after deleting log shipping there are residual data left behind. In the initialization stage did you uncheck the box labelled "Enable this as a primary database in a log shipping configuration" before taking a full backup?
    – Raidenlee
    Aug 30, 2016 at 17:53
  • I have completely deleted the configuration and recreated it with the checkbox enabled that you spoke of and it still remains. How can I clear all this info and start from fresh? Is deleting and recreating the only way to conceivably do this? If so, that doesn't appear to work in my case.
    – AKDiscer
    Aug 30, 2016 at 18:35
  • 1
    There are a lot of ways to resync but i chose to give the most time efficient methods. Checking for residual data, after deleting log shipping you should run queries: Select * from msdb.dbo.log_shipping_monitor_primary and select * from msdb.dbo.log_shipping_monitor_secondary in their respective environment. If there is data then run these in their respective environment: Delete from msdb.dbo.log_shipping_monitor_primary where primary_database='logshippingdatabasename' or Delete from msdb.dbo.log_shipping_monitor_secondary where secondary_database='logshippingdatabasename'
    – Raidenlee
    Aug 30, 2016 at 19:06
  • @Rockless I'll give that a try and see if things differ, thank you for your effort!
    – AKDiscer
    Aug 30, 2016 at 19:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.