question

edwardhall avatar image
edwardhall asked

i have setup log shipping and most databses work but a few dont i can not get thenm to work. what am i missing?

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:


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 '\\secondary\SQLLogShip\secondarydb_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 '\\secondary\SQLLogShip\secondarydb_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 '\\secondary\SQLLogShip\secondarydb_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 '\\secondary\SQLLogShip\secondarydb_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 '\secondary\SQLLogShip\dbname_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?


log-shipping
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

edwardhall avatar image edwardhall commented ·

still a problem rebuild log shipping and it runs good until the 3rd cycle when it gets the error. some databses from the primary to secondary work just fine but this one doesnt. is there something in the database that could cause this failure?

0 Likes 0 ·
JohnM avatar image JohnM commented ·

That last error about recovery sounds suspicious. Is this still an issue? What is being shown in the server error log if anything?

0 Likes 0 ·

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.