3

I have a problem with Transaction Log Shipping on the SQL Server 2014 edition. I have to log ship 4 databases, 2 of them are fine and the other 2 ALWAYS fail but not at first. When it fails, SQL reports that one of the log backups in the chain "could not be verified" thus breaking the chain. These 2 databases in question will also end up in a "Restoring" state. All the articles that I have read about this issue report that I should put the database in emergency mode and run a DBCC CheckDB, but I can't since the database is in restoring state...

Additionally, I have verified that the broken log backup in question is valid by running a RESTORE with VERIFYONLY, and no errors are reported.

I have set up log shipping on these two databases up at least 7 times now, always to have it break in a few hours. What can cause this behavior?

Here are some of the logs. This first one is the last successful restore after setting log shipping up again:

2016-11-23 15:41:53.66 Restored log backup file. Secondary DB: 'XXX', File: 'XXX.trn'

And then the very next message is

2016-11-23 15:41:55.29    *** Error: Could not apply log backup file 'XXX.trn' to 
secondary database 'XXX'.(Microsoft.SqlServer.Management.LogShipping) ***
*** Error: An error occurred while processing the log for database
'text.staging.fsintel.com'.  
If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.

Both instances are on SP2 GDR.

Improve this question
4
  • 1
    Have you tried manually restoring the backups/logs to a different server? Does the log chain break in the same spot?
    – AMtwo
    Apr 9, 2017 at 18:01
  • You could try taking a differential backup on Primary, restoring that to your log ship secondary with norecovery, then try to resume applying logs. That would confirm if the log chain is simply broken, or if the secondary database is broken.
    – AMtwo
    Apr 9, 2017 at 18:02
  • Any events logged into windows error logs or sql server error logs ?
    – Kin Shah
    Aug 7, 2018 at 18:23
  • Could you please provide schedule of full, differential and log backup at primary server and also schedule of log backup, copy backup and restoration of backup through log-shipping job.
    – Learning_DBAdmin
    Sep 11, 2019 at 5:45

1 Answer 1

Reset to default
1 
An error occurred while processing the log for database 

'text.staging.fsintel.com'. If possible, restore from backup. If a backup is not 

available, it might be necessary to rebuild the log.

Somehow your logs are getting miss matched.

  1. If you have any Log backups job please disable it.
  2. Avoid taking log backups while enabled log shipping, or just manually run the logshipping backup job for log backups.
Improve this answer

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.