Help: SQL Server

During my last visit to one of my friend’s home I was surprised to hear that he had a problem in log shipping where he wanted my help. It was a family meet-up and I never thought it would become technical. Anyways, I asked the problem and he mentioned that he has configured log-shipping for one database between two servers. He told me that he was getting some “delay” error and I asked to give the exact error message. Below is what we saw in “Restore Job history”

2014-06-21 06:14:30.45     Starting transaction log restore. Secondary ID: ‘8a3c7cae-6df2-4c6e-b528-2062d4b830f0’
2014-06-21 06:14:30.45     Retrieving restore settings. Secondary ID: ‘8a3c7cae-6df2-4c6e-b528-2062d4b830f0’
2014-06-21 06:14:30.45     Retrieved common restore settings. Primary Server: ‘SQLPAPA’, Primary Database: ‘LogShipDemo’, Backup Destination Directory: ‘E:\LS\Secondary’, File Retention Period: 4320 minute(s)
2014-06-21 06:14:30.45     Retrieved database restore settings. Secondary Database: ‘SomeName’, Restore Delay: 2, Restore All: True, Restore Mode: No Recovery, Disconnect Users: False, Last Restored File: E:\LS\Secondary\SomeName_20140621004200.trn, Block Size: Not Specified, Buffer Count: Not Specified, Max Transfer Size: Not Specified
2014-06-21 06:14:30.51     Skipping log backup file since load delay period has not expired. Secondary DB: ‘SomeName’, File: ‘E:\LS\Secondary\SomeName_20140621004231.trn’
2014-06-21 06:14:30.51     The restore operation was successful. Secondary Database: ‘SomeName’, Number of log backup files restored: 0
2014-06-21 06:14:30.51     Deleting old log backup files. Primary Database: ‘SomeName’
2014-06-21 06:14:30.51     The restore operation was successful. Secondary ID: ‘8a3c7cae-6df2-4c6e-b528-2062d4b830f0’
2014-06-21 06:14:30.51     —– END OF TRANSACTION LOG RESTORE     —–

So the question he asked were:

• What is this "Load delay period" and what’s the meaning of the error?
• Can we configure this somehow, somewhere?

Answer

• Load delay is a period what is given to secondary server to wait to restore the backup file. We can use this if we want to wait upon the copy job.
• We can set this value in the SSMS
  Database > Right click > properties -> Transaction Log shipping -> Secondary databases -> Restore Transaction Log

If we want to check via T-SQL then we can see that in MSDB database of secondary server.

Select  secondary_database, 
        restore_delay 
from    msdb.dbo.log_shipping_secondary_databases

If the load delay is set to zero and still we are seeing a long delay in restore job picking the file to restore then next thing to check would be if there is a time difference between primary and secondary servers. Having different time zone is not a problem but but UTC time should match else we would see such problems.

Hope this helps!