Log shipping of SQL2012 databases failing
-
In my environment I have two servers that host several production databases each - for this example I shall call them PROD1 and PROD2 I also have two servers that host logshipped copies of the production databases - one for reporting by users, the second for report development by the IT team. These I shall call REP1 and REP2. The setup is as follows: PROD1 - Logships 4 databases to REP1, logships same 4 databases to REP2 - databases on REP1 and REP2 set to Restore with Standby PROD2 - Logships 4 other databases to REP1, logships same 4 databases to REP2 - databases on REP1 and REP2 set to Restore with Standby Logshipping copy jobs are set to copy the transaction logs to a NAS device, to different folders for each of the reporting servers. REP1 and REP2 therefore each host 8 logshipped copies of the production databases, set to Standby/Readonly Recently, 2 of the logshipping restore jobs from PROD1 to REP1 have started to fail on a regular basis, and the equivalent two jobs, plus two of the jobs from PROD2 to REP2 have started failing on REP2. The job histories for the failing jobs don't show aas failed jobs, as the database connection of the jobs seems to be lost, meaning that logging of the rrors fails. The error in the history are as follows:
Date 09/01/2014 07:30:00
Log Job History (LSRestore_PROD1_dBFirst)Step ID 1
Server REP1
Job Name LSRestore_PROD1_dBFirst
Step Name Log shipping restore log job step.
Duration 00:01:56
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0Message
2014-01-09 07:31:55.93 *** Error: Could not apply log backup file '\\nas2\dbbackups\TransactionLog\DBFirst_20140108210000.trn' to secondary database 'ReportdbFirst'.(Microsoft.SqlServer.Management.LogShipping) ***
2014-01-09 07:31:55.93 *** Error: An error occurred while processing the log for database 'ReportdbFirst'. 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 'ReportdbFirst' (13: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 'ReportdbFirst', file 'livesystem_Data' on file 1.
Processed 1 pages for database 'ReportdbFirst', file 'livesystem_Log' on file 1.(.Net SqlClient Data Provider) ***
2014-01-09 -
In my environment I have two servers that host several production databases each - for this example I shall call them PROD1 and PROD2 I also have two servers that host logshipped copies of the production databases - one for reporting by users, the second for report development by the IT team. These I shall call REP1 and REP2. The setup is as follows: PROD1 - Logships 4 databases to REP1, logships same 4 databases to REP2 - databases on REP1 and REP2 set to Restore with Standby PROD2 - Logships 4 other databases to REP1, logships same 4 databases to REP2 - databases on REP1 and REP2 set to Restore with Standby Logshipping copy jobs are set to copy the transaction logs to a NAS device, to different folders for each of the reporting servers. REP1 and REP2 therefore each host 8 logshipped copies of the production databases, set to Standby/Readonly Recently, 2 of the logshipping restore jobs from PROD1 to REP1 have started to fail on a regular basis, and the equivalent two jobs, plus two of the jobs from PROD2 to REP2 have started failing on REP2. The job histories for the failing jobs don't show aas failed jobs, as the database connection of the jobs seems to be lost, meaning that logging of the rrors fails. The error in the history are as follows:
Date 09/01/2014 07:30:00
Log Job History (LSRestore_PROD1_dBFirst)Step ID 1
Server REP1
Job Name LSRestore_PROD1_dBFirst
Step Name Log shipping restore log job step.
Duration 00:01:56
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0Message
2014-01-09 07:31:55.93 *** Error: Could not apply log backup file '\\nas2\dbbackups\TransactionLog\DBFirst_20140108210000.trn' to secondary database 'ReportdbFirst'.(Microsoft.SqlServer.Management.LogShipping) ***
2014-01-09 07:31:55.93 *** Error: An error occurred while processing the log for database 'ReportdbFirst'. 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 'ReportdbFirst' (13: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 'ReportdbFirst', file 'livesystem_Data' on file 1.
Processed 1 pages for database 'ReportdbFirst', file 'livesystem_Log' on file 1.(.Net SqlClient Data Provider) ***
2014-01-09Chris Quinn wrote:
Recently, 2 of the logshipping restore jobs from PROD1 to REP1 have started to fail on a regular basis
Usually when I see this sort of thing when it appears that connections are failing in odd ways I immediately presume that it is a firewall problem. Of course unless you are firewall administrator getting someone to admit that they did in fact change something in the firewall can be problematic.
-
Chris Quinn wrote:
Recently, 2 of the logshipping restore jobs from PROD1 to REP1 have started to fail on a regular basis
Usually when I see this sort of thing when it appears that connections are failing in odd ways I immediately presume that it is a firewall problem. Of course unless you are firewall administrator getting someone to admit that they did in fact change something in the firewall can be problematic.
The servers are all behind the same firewall, so I think this is unlikely
========================================================= I'm an optoholic - my glass is always half full of vodka. =========================================================
-
The servers are all behind the same firewall, so I think this is unlikely
========================================================= I'm an optoholic - my glass is always half full of vodka. =========================================================
Chris Quinn wrote:
The servers are all behind the same firewall
And you are positive that that statement means that the traffic in fact doesn't go through the firewall? (Versus firewall rules that 'should' allow it to behave as though the firewall wasn't impacting it.)