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.