Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

First time at posting a question on here, so please bear with me and I'll try and detail all the information.

Currently, I'm log shipping from SQL 2012 Standard Edition (11.0.3000) (lets call it ServerA) to another server in a data centre in a different domain (ServerDR) which is also SQL 2012 Standard Edition (11.0.3128). This log shipping is working fine and is reporting fine on the history.

When I then log ship from ServerA to another server (we'll call this ServerB) within the same network / domain I run into some issues. This is also SQL 2012 Standard Edition (11.0.3000), but ServerB is also used for Reporting duties. These only one of these databases will be reported against, the others are purely for a warm backup.

There is 7 databases being logshipped from ServerA to ServerDR and the same 7 databases are being logshipped from ServerA to ServerB. When logshipping from ServerA to ServerB, 6 of those databases will have issues when trying to restore the transaction log backup's, leaving the databases on ServerB in a restoring state, when I have set this to be left in standby / Read only mode.

Looking through the logs, this will happen. First part is the successful restore of the log, then the next will error:

2013-12-27 15:16:03.49 Restored log backup file. Secondary DB: 'Logship_xxxxx', File: 'F:\Logshipped Files\xxxxx_20131224171511.trn'

2013-12-27 15:16:05.82 * Error: Could not apply log backup file 'F:\Logshipped Files\xxxxx_20131224171511.trn' to secondary database 'Logship_xxxxx'.(Microsoft.SqlServer.Management.LogShipping) 2013-12-27 15:16:05.82 Error: An error occurred while processing the log for database 'Logship_xxxxx'. If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log. An error occurred during recovery, preventing the database 'Logship_xxxxx' (27:0) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support. RESTORE LOG is terminating abnormally. Processed 0 pages for database 'Logship_xxxxx', file 'xxxxx' on file 1. Processed 1 pages for database 'Logship_xxxxx', file 'xxxxx' on file 1.(.Net SqlClient Data Provider) 2013-12-27 15:16:05.82 Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) 2013-12-27 15:16:05.82 Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) * 2013-12-27 15:16:05.82 Skipping log backup file 'F:\Logshipped Files\xxxxx_20131224173011.trn' for secondary database 'Logship_xxxxx' because the file could not be verified. 2013-12-27 15:16:05.82 * Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) 2013-12-27 15:16:05.82 Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) 2013-12-27 15:16:05.82 Error: Could not apply log backup file 'F:\Logshipped Files\xxxxx_20131224174511.trn' to secondary database 'Logship_xxxxx'.(Microsoft.SqlServer.Management.LogShipping) 2013-12-27 15:16:05.82 Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) 2013-12-27 15:16:05.82 Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) 2013-12-27 15:16:05.82 Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) * 2013-12-27 15:16:05.82 Skipping log backup file 'F:\Logshipped Files\xxxxx_20131224174511.trn' for secondary database 'Logship_xxxxx' because the file could not be verified. 2013-12-27 15:16:05.82 * Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) 2013-12-27 15:16:05.82 Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) 2013-12-27 15:16:05.82 Error: Could not apply log backup file 'F:\Logshipped Files\xxxxx_20131224180010.trn' to secondary database 'Logship_xxxxx'.(Microsoft.SqlServer.Management.LogShipping) 2013-12-27 15:16:05.82 Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) 2013-12-27 15:16:05.82 Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) 2013-12-27 15:16:05.82 Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) * 2013-12-27 15:16:05.82 Skipping log backup file 'F:\Logshipped Files\xxxxx_20131224180010.trn' for secondary database 'Logship_xxxxx' because the file could not be verified. 2013-12-27 15:16:05.82 * Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) 2013-12-27 15:16:05.82 ** Error: ExecuteNon

My thinking is, there is no issue with the transaction log backups, otherwise ServerDR would be suffering with the same issues. I have re-created the log ship jobs and restored a known good backup on ServerB, but it will break again.

One thing I have noticed though, is if I manually (and painfully) restore each transaction log backup, it still throws errors when using this T-SQL:

RESTORE LOG Logship_xxxxx FROM DISK='F:\Logshipped Files\xxxxx_20131224164516.trn' WITH STANDBY='F:\Logshipped Files\UNDO\UNDO_xxxxx_ARCHIVE.DAT'

Which will give an error of:

Processed 0 pages for database 'Logship_xxxxx', file 'xxxxx' on file 1. Processed 1 pages for database 'Logship_xxxxx', file 'xxxxx_log' on file 1. Msg 9004, Level 16, State 6, Line 1 An error occurred while processing the log for database 'Logship_xxxxx'. If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log. Msg 3013, Level 16, State 1, Line 1 RESTORE LOG is terminating abnormally. Msg 3414, Level 21, State 1, Line 1 An error occurred during recovery, preventing the database 'Logship_xxxxx' (27:0) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.

But, if I keep working through each transaction log, sometimes I'll get:

Processed 0 pages for database 'Logship_xxxxx', file 'xxxxx' on file 1. Processed 1 pages for database 'Logship_xxxxx', file 'xxxxx_log' on file 1. RESTORE LOG successfully processed 1 pages in 0.042 seconds (0.034 MB/sec).

This will put the database back into standby mode, for it to only break again :-( I have also noticed that it doesn't break on the log shipping, when the transaction log files are larger than 10kb or so in size.

Any ideas on this one at all? Apologies for the long post and the somewhat iffy formatting.

share|improve this question
    
What happens if you run RESTORE VERIFYONLY against the problematic log backup file? It sounds like that particular log backup file is corrupt. –  Mike Fal Dec 27 '13 at 16:18
    
But if that log file was corrupt, surely ServerDR would suffer the same fate? It's the same log file that is being shipped to ServerDR as it is to ServerB. –  HBollah Dec 27 '13 at 16:22
    
Just to add to that, I have tried RESTORE VERIFYONLY on a bunch of the transaction logs and they all return 'The backup set on file 1 is valid' –  HBollah Dec 27 '13 at 16:28
1  
It sounds like you have a problem with one log file, or am I misunderstanding? What if you try the command on that log file. Additionally, each log shipping job will copy the files to its own directory. As such, the file copied for ServerB might have been corrupted where the ServerDR was copied fine. –  Mike Fal Dec 27 '13 at 18:15
    
Hi Mike, it's not just the one log file that is the issue. I have checked the file size between ServerB and ServerDR and they are both the same. I have also ran a command fn_dump_dblog and compared the 2 files on both servers and they are the same. I also noticed there was a similar questions asked on here a while back with the same issue link. I appreciate the help you are giving, fresh set of eyes makes a big difference. –  HBollah Dec 30 '13 at 9:23
show 4 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.