Sql-server – log shipping error

log-shippingsql server

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.

Best Answer

Is your log shipping secondary configured in standby mode?

If yes be sure to check this flag: enter image description here

Eventually take a look at the backup history to find intruders in the log chains:

DECLARE @db_name VARCHAR(100)
SELECT @db_name = 'DBname'
-- query
SELECT TOP (30) s.database_name
,m.physical_device_name
,CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS     bkSize
,CAST(DATEDIFF(second, s.backup_start_date, s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken
,s.backup_start_date
,CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn
,CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn
,CASE s.[type] WHEN 'D'
THEN 'Full'
WHEN 'I'
THEN 'Differential'
WHEN 'L'
THEN 'Transaction Log'
END AS BackupType
,s.server_name
,s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = @db_name
ORDER BY backup_start_date DESC
,backup_finish_date