1

Yesterday, I configured Log shipping between 2 SQL Server 2012 SP3 servers for a total of 13 databases.

Some specifics:

  • I'm using 'Azure files' to transfer the log files (CIFS accessible BLOB storage)
  • I did the configuration using SSMS
  • All backups got transferred and restored just fine
  • STANDBY mode (and disconnect users) was used on all databases, I verified this in msdb.dbo.log_shipping_secondary_databases
  • The log shipping process worked for all databases yesterday (they were all in read only mode after the first transaction logs were replayed on the secondary (also verified in msdb.dbo.log_shipping_secondary_databases).

This morning, I noticed the following:

  • Only 5 out of my 13 databases are in read only mode.
  • The rest is stuck in 'restoring' state.
  • The databases that are stuck, are those that have a very low load (or possibly no load at all)
  • In msdb.dbo.log_shipping_secondary_databases, the last_restored_date is not current with the last execution of the agent jobs for the DBs that are stuck.
  • The agent jobs seem to have executed without a problem (job succeeded) on those databases.

Can somebody give me a clue as to what's going on here?

  • Initially, try running backup, copy and restore jobs one by one and check if each one succeeds and does its job. – SQLPRODDBA May 27 '16 at 13:02
  • I did this and analyzed the output of the restore job, and came up with this: Pastebin. There is clearly something wrong here, I'm just wondering now why this is happening. 'the file cannot be verified' seems like a weird error. What cannot be verified about it? – H. Lowette May 27 '16 at 14:21
  • Can you do restore verifyonly of your log backups just to make sure they are restorable. I found similar issue on this dba.stackexchange.com/questions/36135/…. Try those suggestions as well. – SQLPRODDBA May 27 '16 at 14:52
  • Few more things I would like to suggest, if your log backups are valid try doing restores with norecovery option to make sure issue is with only STANDBY option. Also, I am assuming this is not an corruption issue. If it is then it is better to rebuild logshipping rather than fixing the corruption. This link should also help you out for suggestions: social.technet.microsoft.com/Forums/windowsserver/en-US/… – SQLPRODDBA May 27 '16 at 15:22
  • Thanks, SQLPRODDBA. That question confirms my situation. Databases without load go into this failure mode, the others don't. I've confirmed the backups not to be corrupt. So I'll switch to NO RESTORE. – H. Lowette May 30 '16 at 14:50
1

Please do restore verifyonly of your log backups just to make sure they are restorable. Your issue is similar to the one defined in Log Shipping - RESTORE WITH STANDBY - on SQL Server 2012 keeps breaking. Try those suggestions as well.

Few more things I would like to suggest is, if your log backups are valid try doing restores with NORECOVERY option to make sure the issue is with only STANDBY option. Also, I am assuming this is not an corruption issue. If it is then it is better to rebuild logshipping rather than fixing the corruption. This link should also help you out for some more suggestions: https://social.technet.microsoft.com/Forums/windowsserver/en-US/9c39fc61-0a59-402b-91fb-17ac1f03da7b/sql-2012-log-shipping-to-report-server?forum=sqldisasterrecovery

  • 1
    There was indeed no corruption. The same symptoms are showing. We'll be switching to NORECOVERY. – H. Lowette May 30 '16 at 15:13

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.