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.

We are using log shipping and RESTORE WITH STANDBY on SQL Server 2012 in order to restore the database in read-only mode for reporting purposes. However, the log shipping setup keeps breaking after completing a restore of one or two log backups. Log-shipping only breaks when it is running as RESTORE WITH STANDBY; RESTORE WITH NORECOVERY does not cause any problems.

My only intuition about this is that the primary database is not that dynamic. Therefore, when there are no transactions, this causes issues with the RESTORE process, maybe?

Any ideas, known fixes?

I had it working for a few days by running a regular job that does heavy updating on two tables. When the job stopped running the log shipping setup quickly failed, unable to process the .trn file. I reset log-shipping and tried to see if it would keep running by just doing a small update, changing the value of one column of one record in a table, whoever it still failed.

Thanks for all your responses.

PS: An excerpt from our log

02/25/2013 13:00:00,LSRestore_DBDB01-A_BulldogDB,In Progress,1,DBREPORTS,LSRestore_DBDB01-A_BulldogDB,Log shipping restore log job step.,,2013-02-25 13:00:12.31    *** Error: Could not apply log backup file '\\dbsan01\DBBackups\LSBackup_BulldogDB\BulldogDB_20130225180000.trn' to secondary database 'BulldogDB'.(Microsoft.SqlServer.Management.LogShipping) ***
2013-02-25 13:00:12.31  *** Error: An error occurred while processing the log for database 'BulldogDB'.  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 'BulldogDB' (8: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 'BulldogDB' file 'BulldogDB' on file 1.
Processed 1 pages for database 'BulldogDB' file 'BulldogDB_log' on file 1.(.Net SqlClient Data Provider) ***
2013-02-25 13:00:12.32  *** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
2013-02-25 13:00:12.32  *** Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) ***
2013-02-25 13:00:12.32  Skipping log backup file '\\dbsan01\DBBackups\LSBackup_BulldogDB\BulldogDB_20130225180000.trn' for secondary database 'BulldogDB' because the file could not be verified.
2013-02-25 13:00:12.32  *** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
2013-02-25 13:00:12.32  *** Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) ***
2013-02-25 13:00:12.33  *** Error: An error occurred restoring the database access mode.(Microsoft.SqlServer.Management.LogShipping) ***
2013-02-25 13:00:12.33  *** Error: ExecuteScalar requires an open and available Connection. The connection's current state is closed.(System.Data) ***
2013-02-25 13:00:12.33  *** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
2013-02-25 13:00:12.33  *** Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) ***
2013-02-25 13:00:12.33  *** Error: An error occurred restoring the database access mode.(Microsoft.SqlServer.Management.LogShipping) ***
2013-02-25 13:00:12.33  *** Error: ExecuteScalar requires an open and available Connection. The connection's current state is closed.(System.Data) ***
2013-02-25 13:00:12.33  *** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
2013-02-25 13:00:12.33  *** Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) ***
2013-02-25 13:00:12.33  Deleting old log backup files. Primary Database: 'BulldogDB'
2013-02-25 13:00:12.33  *** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
2013-02-25 13:00:12.33  *** Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) ***,00:00:12,0,0,,,,0
share|improve this question
1  
Breaking how? What's the error message, if any? –  Jon Seigel Mar 7 '13 at 4:37
    
It breaks in that the LS_Restore job cannot apply the transaction log backup. I just reset the log-shipping so all the error log information is gone from the database. I have them saved somewhere, I'll post them when I can find them. Thanks. –  Mendel Mar 7 '13 at 4:48
    
Are the original .trn files OK (have you checked them for corruption)? Have you checked your backup files using a separate machine (following a complete restore procedure)? Have you checked your network (in case the original .trn files are ok, but the copies are corrupted)? –  Marian Mar 7 '13 at 9:30
    
We get something like "Skipping log backup file ... .trn for secondary database 'DB' because the file could not be verified. I don't know how you would specifically check for corruption. –  Mendel Mar 7 '13 at 15:19
    
Was wondering if you/anyone has ever found a solution or work-around to this? I'm experiencing exactly the same problem. Log shipping between two sql 2012 sp1 servers, eventualy fails on a single .trn file, with your exact error message. Restoring the file manually "WITH STANDBY" fails as well, while restoring "WITH NO RECOVERY" works - just like for you. One item of note is that our primary (source) SQL server has been through several upgrades before becoming 2012 SP1 (was originally 2008 r2, etc...). Was yours a fresh sql install, or an upgraded server? –  user24662 Jun 6 '13 at 18:21

1 Answer 1

If the log backup's are able to be restored while the secondary database is in NORECOVERY and only fails when it is in READ-ONLY/STANDBY then I would assume the log backups themselves are ok and not corrupted.

It could be that your reporting component has an open connection to the database therefore when restoring the log file it is unable to obtain an exclusive connection to the database due to the open connections. There would be an option when setting up log shipping to disconnect any connections to allow it to restore the log backup.

share|improve this answer

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.