i have several databases log shipped from primary to secondary servers. most work fine however i have one that fails after 1 or 2 cycles. log shipping is on a 15 min schedule. i have been trying to find the cause of the failure with little luck. as i say not all log shipped databases fail from primary to secondary. i have compared settings and try monitoring. i see once it fails it gets stuck and needs resetup to get it back but it will quickly fail.
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) – Ed Hall Jan 25 at 14:23
here is the latest failure message:
021-01-29 09:21:04.48 *** Error: Could not apply log backup file '\\Secondary\SQLLogShip\DBname_20210129141502.trn' to secondary database 'DBname'.(Microsoft.SqlServer.Management.LogShipping) ***
2021-01-29 09:21:04.48 *** 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 'DBname' (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_log' on file 1.(.Net SqlClient Data Provider) ***
2021-01-29 09:21:04.48 *** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
2021-01-29 09:21:04.52 *** Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) ***
2021-01-29 09:21:04.54 Skipping log backup file '\\Secondary\SQLLogShip\DBname_20210129141502.trn' for secondary database 'DBname' because the file could not be verified.
2021-01-29 09:21:04.54 *** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
2021-01-29 09:21:04.54 *** Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) ***
2021-01-29 09:21:04.54 *** Error: An error occurred restoring the database access mode.(Microsoft.SqlServer.Management.LogShipping) ***
2021-01-29 09:21:04.54 *** Error: ExecuteScalar requires an open and available Connection. The connection's current state is closed.(System.Data) ***
2021-01-29 09:21:04.54 *** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
2021-01-29 09:21:04.54 *** Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) ***
2021-01-29 09:21:04.54 *** Error: An error occurred restoring the database access mode.(Microsoft.SqlServer.Management.LogShipping) ***
2021-01-29 09:21:04.54 *** Error: ExecuteScalar requires an open and available Connection. The connection's current state is closed.(System.Data) ***
2021-01-29 09:21:04.54 *** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
2021-01-29 09:21:04.54 *** Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) ***
2021-01-29 09:21:04.54 Deleting old log backup files. Primary Database: 'DBname'
2021-01-29 09:21:04.54 *** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
2021-01-29 09:21:04.54 *** Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) ***
2021-01-29 09:21:04.54 Deleting log backup file. File: '\\Secondary\SQLLogShip\DBname_20210128141501.trn', Last Write Time (UTC): '1/28/2021 2:15:02 PM'
2021-01-29 09:21:04.54 *** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
2021-01-29 09:21:04.54 *** Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) ***
What am i missing to get this log shipping to work.
how do i find details on the error so i can eliminate it.
RESTORE VERIFYONLY
to eliminate whether the backups themselves are becoming corrupted. On the Primary copy of the database, ensure that you have successfully runDBCC CHECKDB
. Report back with the outcomes of these.