I have transaction log shipping configured between two SQL Servers, where the secondary database is in Standby / Read-only. Transaction backup, copy and restore is performed every 10 minutes. The old logs are keeped for a day then deleted. Everything is working fine until I make a closer look to the restore job and log shipping monitor status.
Restore job is searching for a correct transaction log in the number of steps containing the following message:
Skipped log backup file. Secondary DB: 'DBName', File: '\\SERVER\LogShip\DBName_20160728230001.trn'
After the scan of all transaction logs it founds the last/actual one:
Found a log backup file to apply. Secondary Database: 'DBName', File: '\\SERVER\LogShip\DBName_20160728233001.trn'
And it succesfully restore:
2016-07-29 01:33:39.59 Restored log backup file. Secondary DB: 'DBName', File: '\\SERVER\LogShip\DBName_20160728233001.trn' 2016-07-29 01:33:39.65 The restore operation was successful. Secondary Database: 'DBName', Number of log backup files restored: 1 2016-07-29 01:33:39.66 Deleting old log backup files. Primary Database: 'DBName'
It usually takes more than a minute to found a correct log and during that time the secondary DB is not available which is not very good.
Another sign that something is configured wrong is the output of
SELECT * FROM [msdb].[dbo].[log_shipping_secondary_databases]
which is as follows:
DBName XXXXXXXXX 0 1 1 1 -1 -1 -1 \\SERVER\LogShip\DBName_20151113071001.trn 2015-11-13 08:12:21.303
as you can see SQL Server seems to have a no idea about the restores performed during the LSRestore job.
The log shipping report on the primary database has the following results:
- Backup, Time Since Last - 4 min
- Copy, Time Since Last - 372983 min
- Restore, Time Since Last - 372982 min
- Restore, Latency of Last File - 2 min
which is really strange, since the files are being copied without any problem between servers.