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?

Improve this question
5
  • Initially, try running backup, copy and restore jobs one by one and check if each one succeeds and does its job.
    – SQLPRODDBA
    May 27, 2016 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, 2016 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, 2016 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, 2016 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, 2016 at 14:50

1 Answer 1

Reset to default
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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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