sql server - Log shipping restore job failing after a couple successful runs

Question

I have log shipping on several databases that are working good and I have three that fail. They are all going from the same primary to the same secondary. I have mimicked the settings of the working databases to the nonworking databases.

I resetup delete and resetup the log shipping for the failing database and the restore works as it should the first couple of times. But usually on the third cycle it posts disconnecting user in the job history then from then on it takes errors. The job completes successfully but with errors in the restore step.

What am I missing here? After the error i run a full backup and restore leaving it in standby mode then the next log shipping cycle is successful. but the 2nd cycle fails again

I'm using version: ---Microsoft SQL Server 2012 (SP4-GDR) (KB4532098) - 11.0.7493.4 (X64) Dec 24 2019 19:33:24 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor) The job history log:

021-01-25 08:17:06.30   *** Error: Could not apply log backup file '\secondarySQLLogShipsecondarydb_20210125034500.trn' to secondary database 'secondarydb'.(Microsoft.SqlServer.Management.LogShipping) ***
2021-01-25 08:17:06.30  *** Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) ***
2021-01-25 08:17:06.30  *** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
2021-01-25 08:17:06.30  *** Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) ***
2021-01-25 08:17:06.30  Skipping log backup file '\secondarySQLLogShipsecondarydb_20210125034500.trn' for secondary database 'secondarydb' because the file could not be verified.
2021-01-25 08:17:06.30  *** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
2021-01-25 08:17:06.30  *** Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) ***
2021-01-25 08:17:06.30  *** Error: An error occurred restoring the database access mode.(Microsoft.SqlServer.Management.LogShipping) ***
2021-01-25 08:17:06.30  *** Error: ExecuteScalar requires an open and available Connection. The connection's current state is closed.(System.Data) ***
2021-01-25 08:17:06.30  *** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
2021-01-25 08:17:06.30  *** Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) ***
2021-01-25 08:17:06.30  *** Error: Could not apply log backup file '\secondarySQLLogShipsecondarydb_20210125040001.trn' to secondary database 'secondarydb'.(Microsoft.SqlServer.Management.LogShipping) ***
2021-01-25 08:17:06.30  *** Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) ***
2021-01-25 08:17:06.30  *** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
2021-01-25 08:17:06.30  *** Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) ***
2021-01-25 08:17:06.30  Skipping log backup file '\secondarySQLLogShipsecondarydb_20210125040001.trn' for secondary database 'secondarydb' because the file could not be verified.
2021-01-25 08:17:06.30  *** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
2021-01-25 08:17:06.30  *** Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) ***
2021-01-25 08:17:06.30  *** Error: An error occurred restoring the database access mode.(Microsoft.SqlServer.Management.LogShipping) ***
2021-01-25 08:17:06.30  *** Error: ExecuteScalar requires an open and available Connection. The connection's current state is closed.(System.Data) ***
2021-01-25 08:17:06.30  *** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
2021-01-25 08:17:06.30  *** Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) ***

I rebuilt the log shipping and disabled the 2nd log backup job. got new error. 2021-01-27 07:18:04.37 *** Error: Could not apply log backup file 'secondarySQLLogShipdbname_20210126204500.trn' to secondary database 'dbname'.(Microsoft.SqlServer.Management.LogShipping) *** 2021-01-27 07:18:04.37 *** Error: An error occurred while processing the log for database 'dbname'. 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 'DP_Rpt' (19: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 'dbname', file 'dbname' on file 1. Processed 1 pages for database 'dbname', file 'dbname' on file 1.(.Net SqlClient Data Provider) *** 2021-01-27 07:18:04.39 *** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***

What is going on? i have several logshipped databases tht wrok just fine but this one has issues.

would a daily full backup job running on the primary cause this issue?

question from:https://stackoverflow.com/questions/65885807/log-shipping-restore-job-failing-after-a-couple-successful-runs

Answer 1

Waitting for answers