11

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
3
  • 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, 2013 at 4:48
  • 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, 2013 at 15:19

2 Answers 2

4

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.

1
  • 1
    This is correct. To get around this don't let the logshipping jobs restore with standby but add a second step to the job that will restore with standby. RESTORE DATABASE [database] with STANDBY = N'standbyfile'. Another option would be that the transaction log backup is not finished, try adding 20 min delay for the restores
    – Spörri
    May 21, 2015 at 9:26
1

On standby Secondary restore only disconnect users when job starts, after start users can connect ...and this will stop restore process with error about "exclusive acces" . I got service that tried to connect to standby database while in restore. That made restore process to break after 1-10/100 restored files.

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.