Sql-server – Issues with Log Shipping

log-shippingsql-server-2012

First time at posting a question on here, so please bear with me and I'll try and detail all the information.

Currently, I'm log shipping from SQL 2012 Standard Edition (11.0.3000) (lets call it ServerA) to another server in a data centre in a different domain (ServerDR) which is also SQL 2012 Standard Edition (11.0.3128). This log shipping is working fine and is reporting fine on the history.

When I then log ship from ServerA to another server (we'll call this ServerB) within the same network / domain I run into some issues. This is also SQL 2012 Standard Edition (11.0.3000), but ServerB is also used for Reporting duties. These only one of these databases will be reported against, the others are purely for a warm backup.

There is 7 databases being logshipped from ServerA to ServerDR and the same 7 databases are being logshipped from ServerA to ServerB. When logshipping from ServerA to ServerB, 6 of those databases will have issues when trying to restore the transaction log backup's, leaving the databases on ServerB in a restoring state, when I have set this to be left in standby / Read only mode.

Looking through the logs, this will happen. First part is the successful restore of the log, then the next will error:

2013-12-27 15:16:03.49 Restored log backup file. Secondary DB: 'Logship_xxxxx', File: 'F:\Logshipped Files\xxxxx_20131224171511.trn'

2013-12-27 15:16:05.82 * Error: Could not apply log backup file 'F:\Logshipped Files\xxxxx_20131224171511.trn' to secondary database 'Logship_xxxxx'.(Microsoft.SqlServer.Management.LogShipping)
2013-12-27 15:16:05.82 Error: An error occurred while processing the log for database 'Logship_xxxxx'. 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 'Logship_xxxxx' (27: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 'Logship_xxxxx', file 'xxxxx' on file 1.
Processed 1 pages for database 'Logship_xxxxx', file 'xxxxx' on file 1.(.Net SqlClient Data Provider)
2013-12-27 15:16:05.82 Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping)
2013-12-27 15:16:05.82 Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data)
2013-12-27 15:16:05.82 Skipping log backup file 'F:\Logshipped Files\xxxxx_20131224173011.trn' for secondary database 'Logship_xxxxx' because the file could not be verified.
2013-12-27 15:16:05.82 Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping)
2013-12-27 15:16:05.82 Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data)
2013-12-27 15:16:05.82 Error: Could not apply log backup file 'F:\Logshipped Files\xxxxx_20131224174511.trn' to secondary database 'Logship_xxxxx'.(Microsoft.SqlServer.Management.LogShipping)
2013-12-27 15:16:05.82 Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data)
2013-12-27 15:16:05.82 Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping)
2013-12-27 15:16:05.82 Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data)
2013-12-27 15:16:05.82 Skipping log backup file 'F:\Logshipped Files\xxxxx_20131224174511.trn' for secondary database 'Logship_xxxxx' because the file could not be verified.
2013-12-27 15:16:05.82 Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping)
2013-12-27 15:16:05.82 Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data)
2013-12-27 15:16:05.82 Error: Could not apply log backup file 'F:\Logshipped Files\xxxxx_20131224180010.trn' to secondary database 'Logship_xxxxx'.(Microsoft.SqlServer.Management.LogShipping)
2013-12-27 15:16:05.82 Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data)
2013-12-27 15:16:05.82 Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping)
2013-12-27 15:16:05.82 Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data)
2013-12-27 15:16:05.82 Skipping log backup file 'F:\Logshipped Files\xxxxx_20131224180010.trn' for secondary database 'Logship_xxxxx' because the file could not be verified.
2013-12-27 15:16:05.82 Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) *
2013-12-27 15:16:05.82 *** Error: ExecuteNon

My thinking is, there is no issue with the transaction log backups, otherwise ServerDR would be suffering with the same issues. I have re-created the log ship jobs and restored a known good backup on ServerB, but it will break again.

One thing I have noticed though, is if I manually (and painfully) restore each transaction log backup, it still throws errors when using this T-SQL:

RESTORE LOG Logship_xxxxx
FROM DISK='F:\Logshipped Files\xxxxx_20131224164516.trn'
WITH STANDBY='F:\Logshipped Files\UNDO\UNDO_xxxxx_ARCHIVE.DAT'

Which will give an error of:

Processed 0 pages for database 'Logship_xxxxx', file 'xxxxx' on file 1.
Processed 1 pages for database 'Logship_xxxxx', file 'xxxxx_log' on file 1.
Msg 9004, Level 16, State 6, Line 1
An error occurred while processing the log for database 'Logship_xxxxx'. If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
Msg 3414, Level 21, State 1, Line 1
An error occurred during recovery, preventing the database 'Logship_xxxxx' (27: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.

But, if I keep working through each transaction log, sometimes I'll get:

Processed 0 pages for database 'Logship_xxxxx', file 'xxxxx' on file 1.
Processed 1 pages for database 'Logship_xxxxx', file 'xxxxx_log' on file 1.
RESTORE LOG successfully processed 1 pages in 0.042 seconds (0.034 MB/sec).

This will put the database back into standby mode, for it to only break again 🙁 I have also noticed that it doesn't break on the log shipping, when the transaction log files are larger than 10kb or so in size.

Any ideas on this one at all? Apologies for the long post and the somewhat iffy formatting.

Best Answer

I am currently facing the same issue.

If you keep the restoration mode to NO RECOVERY, this should solve the issue.

If you change the restoration mode back to STANDBY you will start getting the same error again.

Related Solutions

Sql-server – Log Shipping – RESTORE WITH STANDBY – on SQL Server 2012 keeps breaking

If the log backup's are able to be restored while the secondary database is in NORECOVERY and only fails when it is in READ-ONLY/STANDBY then I would assume the log backups themselves are ok and not corrupted.

It could be that your reporting component has an open connection to the database therefore when restoring the log file it is unable to obtain an exclusive connection to the database due to the open connections. There would be an option when setting up log shipping to disconnect any connections to allow it to restore the log backup.

Sql-server – Create Log Shipping Step Failed After ServerName Changed

So in SQL Server 2012 SP1 I did this:

EXEC msdb..sp_helptext 'sp_verify_jobstep';

I scanned for 14234 (the error number you received), and right above the relevant RAISERROR call, I saw this:

IF (@server IS NOT NULL) AND (NOT EXISTS (SELECT *
  FROM master.dbo.sysservers
  WHERE (UPPER(srvname) = UPPER(@server))))

So, run this code with a PRINT:

IF NOT EXISTS (SELECT *
  FROM master.dbo.sysservers
  WHERE (UPPER(srvname) = UPPER(N'ServerB')))
BEGIN
  PRINT 'Not found.';
END

Then verify exactly what is in master.dbo.sysservers and also check master.sys.servers. I suspect either those two views don't match or the name in both is not exactly ServerB. Show the actual syntax you used to call the procedure and verify that the server wasn't renamed with leading/trailing spaces, whitespace or other non-printing characters.

Another idea is to not use the system procedure to set up log shipping in the first place. Log shipping isn't all that cumbersome to set up and in the past I've always just set up the backup / restore jobs manually. Also the stored procedure you called first only takes monitor_server as a potential server name where it could get into trouble connecting somehow, and it is nullable. Are you sure you need to pass it? Why would you want the monitor server to be the same as the source server? If one goes down they're both down by definition. What happens when you don't explicitly name a server as the monitor_server and leave that and other related parameters null?

Also these various hooks and potential conflicts are why I don't ever bother to rename servers that are already running. I'd rather pay the extra cost of wiping, reinstalling and restoring than dealing with all of this after the fact - especially if the quick rename was sold as something "hassle-free."

Best Answer

Related Solutions

Sql-server – Log Shipping – RESTORE WITH STANDBY – on SQL Server 2012 keeps breaking

Sql-server – Create Log Shipping Step Failed After ServerName Changed

Related Question