Sql-server – SQL Server Log Shipping Randomly Stops Copying and Restoring

logsql-server-2008-r2transaction

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.

Best Answer

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.

Related Question