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?