SQL Server 2012 log shipping LSRestore searching for transaction log

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.


Category: sql server Time: 2016-07-29 Views: 0

Related post

iOS development

Android development

Python development

JAVA development

Development language

PHP development

Ruby development


Front-end development


development tools

Open Platform

Javascript development

.NET development

cloud computing


Copyright (C) avrocks.com, All Rights Reserved.

processed in 0.222 (s). 12 q(s)