3

Recently I configured Log shipping between 2 different servers (all SQL Server 2008 R2), from primary to secondary (2 instances). Log shipping configured successfully and databases on secondary servers are Standby/Read only mode. I mean from primary to 2 secondary servers I added for Log shipping.

Today I observed Log backup is happening on primary but it's not restoring into secondary. Is the Full Backup on primary breaking Log shipping, or are there any other reasons for breaking Log shipping?

@ Kris Gruttemeyer, Transactional Log shipping status on secondary server seems like below:

enter image description here

Error Message for Restoring data in View Job History while Running Restore Job on Log shipping server (Secondary): "Could not find a log backup file that could be applied to secondary database" and “The restore operation completed with errors. Secondary ID: '807202d2-18a6-4236-85ad-ae3a51fba2eb'”

There is no answer for previous question as well: What does 'Could not find a log backup file that could be applied' means on log shipping?. Please help me on this?

CR241
  • 1,485
  • 3
  • 17
  • 32
  • 2
    The copy job is failing as per the image can you check that. Can you also confirm that backup,copy job is running successfully ? – Shanky Jun 14 '17 at 17:06
  • 1
    Odds are if the copy job has been failing for that long, you may need to re-initialize the whole thing because those backup files were probably removed by the backup retention configuration setting when you initially configured it. Judging by that picture, log shipping has been non-operational for almost a month. – Kris Gruttemeyer Jun 14 '17 at 17:11
  • Copy and Backup jobs are stopped working. So I need to do Re-config the Log Shipping again? – CR241 Jun 14 '17 at 18:08
  • What do you mean by stopped working? Can you read the history and post its content here for both the jobs – Shanky Jun 14 '17 at 18:10
  • Oh I'm sorry, totally confused here. I have a Backup and Alerts jobs on Primary server which running successfully as per schedule. But Copy and Restore jobs on Secondary servers stopped working, I mean there is job History for them almost a month ago. So I need to manually start both jobs on Secondary server again. – CR241 Jun 14 '17 at 18:26
  • I Restarted the SQL server Agent, manually Start jobs on Primary server, it run successfully and then tried same for Secondary server- the jobs on secondary Copy job run successful and Restore & Alert jobs run with error message "The job failed. The Job was invoked by User xxx. The last step to run was step 1 (Log shipping restore log job step.).". What is the exact problem with Restore job here? – CR241 Jun 14 '17 at 18:57
  • You're going to have to expand the job step in the job history. You'll see a slew of retries or something. Each one of those contains a message that actually describes what went wrong. More than likely, its because it's so far out of sync that the next file it needs to restore has long since perished into digital oblivion. I've added a picture below to my answer to show you exactly where to look. – Kris Gruttemeyer Jun 14 '17 at 18:59
  • @KrisGruttemeyer I have only one step for this job and command like "D:\Program Files\Microsoft SQL Server\130\Tools\Binn\sqllogship.exe" -Restore 807202D2-18F4-4236-85AD-AE3A51FBA2EB -server MSSQL8" – CR241 Jun 14 '17 at 19:02
  • Look at the job history from when you just ran it. I posted a picture of what you're looking for. Initially you will just see that it failed but if you expand the node, you'll see several entries below the main entry, within those lies the information of what exactly is going wrong. As I said, I think you may need to re-initialize log shipping entirely as you are probably too far out of sync and the next log file that needs to be restored has been deleted from the system. – Kris Gruttemeyer Jun 14 '17 at 19:03
  • I got you, in restore job final step shows error message "Message Executed as user: Domain_name\SQLLOGSHIP08. The step failed.". and Few more error message in previous steps: Message 2017-06-14 14:15:18.09 *** Error: Could not find a log backup file that could be applied to secondary database 'DB_Name'.(Microsoft.SqlServer.Management.LogShipping) *** 2017-06-14 14:15:18.09 Deleting old log backup files. Primary 2017-06-14 14:15:18.15 The restore operation completed with errors. Secondary ID: '807202d2-18f4-4236-85ad-ae3a51fba2eb' 2017-06-14 14:15:18.15 -- END OF TRANSACTION LOG RESTORE – CR241 Jun 14 '17 at 19:22
  • 1
    Odds are it's because it's TOO far out of sync. Default backup file retention is 7 days I think so if you go beyond that time frame, the log backups no longer exist. Your best bet it to go scorched earth and re-initialize the log shipping secondary. – Kris Gruttemeyer Jun 14 '17 at 20:43
  • Okay, So I need to Delete Secondary servers from Log shipping on Primary and do it again. That what you saying? There is no other options to get Backup file back to action? – CR241 Jun 14 '17 at 20:57
  • I would just remove log shipping secondary and redo it. As I've stated numerous times, the log files you need (for the past month) are long gone. Best to just start from scratch. – Kris Gruttemeyer Jun 14 '17 at 23:36

1 Answers1

3

FULL backups do not break log shipping. FULL backups simply reset your differential base for all subsequent DIFF backups until the next FULL occurs.

Log shipping occurs in 3 phases:

1- Log backup on the primary

2- Copy log file to destination server (or network share)

3- Restore Log to secondary

If using default settings, these each occur in 15 minute intervals. This means that it can take up to 45 minutes for a log file taken on the primary to be successfully applied to the secondary.

An easy way to verify that things are in sync is use the 'Transaction Log Shipping Status Report' on the secondary server by right clicking the server name in SSMS and navigating to the following:

enter image description here

EDIT: Here's where to look:

enter image description here

Kris Gruttemeyer
  • 3,834
  • 1
  • 21
  • 43