2

I'm fairly new to Log Shipping but have configured a few with no issues, however I have a server sitting on SQL 2008 R2 with 6 databases on there, all 6 of these are supposed to be log shipping. The issue I have is that for some reason one of the larger databases stopped shipping so I removed the Log Shipping and reinstated it from scratch for every 15 minutes. First of all my copy jobs are all working fine, it appears to be the restore jobs that are having a problem. Even though I have just taken a backup of one of the databases and restored it to the LS and then kicked of shipping again, the Restore job has a long list of:

"Skipped log backup file. Secondary DB...."

messages and does this for every 15 minute file for the past 24 hours. One of the last messages states:

"Could not find a log backup file that could be applied to secondary database"

What I don't understand is why is log shipping trying to restore all log files for the past 24 hours when I have just taken a full backup.

I get another message stating:

"Could not find a log backup file that could be applied to secondary database"

which I don't understand as I can see files that are sitting there waiting to restore.

1

I spent a while in a test environment trying to reproduce your issue and have had some success. If I set up Log Shipping in its most basic form, and in such a way as to mimic your situation, everything appears to be working. However, I have found one way to configure the environment that results in messages similar to yours...

Message 1: enter image description here

Message 2: enter image description here

Let's walk through how I configured things in order to get the messages above:

  1. Opened the Log Shipping properties on the Primary database.
  2. Clicked "Backup Settings" and entered the same network folder for both input boxes (such as \\NetworkLocation_\DestinationForLogBackups).
  3. Clicked "Ok".
  4. Clicked the elipse (...) button to the right of my Secondary server instance.
  5. Entered the destination network folder for copied files (such as \\NetworkLocation_\DestinationForLogCopies).
  6. Checked the "Disable this job" checkbox down in the lower right.
  7. Clicked "Ok"

The key to causing these errors is number 6 above. This causes the following scenario to take place...

  • The Log Backup job backs up to a location (we'll call it "A").
  • The Copy job does nothing as it is disabled.
  • The Restore job attempts a restore from the location specified in the Copy job tab above (we'll call it "B").
  • The Restore job finds no suitable log files to apply in location B because they are all being backed up to location A and NOT being copied over to B (due to the Copy job being disabled).

The end result is not a clear error message outlining the root cause, but instead misleading messages that cause confusion about what is really happening.

This all seems to fit your situation, except for your comment:

First of all my copy jobs are all working fine, it appears to be the restore jobs that are having a problem.

It would be a good idea to verify whether the Copy job is indeed enabled and is working as expected. Look through the history of that job carefully and see if any of the information above helps you in identifying what your next step should be.

  • Hi there great post thank you, yes my copy jobs are copying over OK I can see the same files sitting on the source and destination servers which suggests to me that there isn't an issue with the copy jobs. Very puzzling. – Andy Curren Jul 8 '15 at 6:16
  • If you can potentially update your question to include more details (screenshots of your configuration with private info marked out, we can try to be a second set of eyes for you on what might be causing this. In the meantime I will try to perform some additional testing on my end to see if I can reproduce it with the Copy job enabled... – Micah Nikkel Jul 8 '15 at 7:25
  • Please verify that the log shipped database is not having its log backed up by any process outside of Log Shipping (ex. change made to maintenance plan that now backs up the log, etc.) – Micah Nikkel Jul 8 '15 at 10:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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