0

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.

3
  • >>>Diagnose the recovery errors and fix them<<< Check the errorlog for errors. It may be corrupted log file on your secondary
    – sepupic
    Jan 29 at 16:28
  • >>>nothing in the error log i looked before i posted<<< If I were you I restored this db with recovery and ran checkdb on it, your log file seems to be corrupted
    – sepupic
    Jan 29 at 19:45
  • For each of the backups from this database (Full and Transaction Log) try running RESTORE VERIFYONLY to eliminate whether the backups themselves are becoming corrupted. On the Primary copy of the database, ensure that you have successfully run DBCC CHECKDB. Report back with the outcomes of these. Jan 31 at 22:37
0

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
9
  • yes it is goin g into standby read only mode and yes it is checked. this error occurs right after 2021-01-29 10:21:01.64 Disconnecting users. Secondary DB: 'Dbname'
    – Ed Hall
    Jan 29 at 17:49
  • nothing in the error log i looked before i posted.
    – Ed Hall
    Jan 29 at 18:39
  • Are you taking others LOG backup or the log shipping log backup is the only one?
    – MBuschi
    Jan 29 at 19:25
  • there is anoter log backup job but this database is excluded. i even disabled it as a test nd the error was the same
    – Ed Hall
    Jan 29 at 19:28
  • please test it with the query above, look up at the physical device name to find different targets
    – MBuschi
    Jan 29 at 19:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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