September 1, 2015 at 2:26 am
Hi all
Our current setup is SQL 2012 Enterprise edition.
Up until recently, our Log Shipping setup was very basic, consisting of a primary and secondary SQL Server. All was running smoothly with no issues.
We've now added a 2nd secondary.
The problem is that the copy job on the 2nd secondary runs for over 2 hours while on the original secondary, it runs for less than a minute. Both secondaries are in the same data center.
On the 2nd secondary, looking at the history of the copy job, I keep seeing this message:
The backup file '\\location\location\file_name.trn' already exists in the destination directory. Secondary ID: '3b018596-7604-490e-8e6c-e1eedcdfb0fc'
I ran the following query on both secondaries:
select last_copied_file,last_copied_date,last_restored_file,last_restored_date
from msdb.dbo.log_shipping_monitor_secondary
On the original secondary, the values are all populated with up to date dates.
On the 2nd secondary, all values return NULL besides last_restored_date.
I'm guessing that this might be causing the copy job on the 2nd secondary to scan the entire directory each time the copy job runs on the 2nd secondary.
Any ideas?
Thanks
September 1, 2015 at 11:09 am
Has the copy job ever run successfully on the new secondary
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 2, 2015 at 12:21 am
Perry Whittle (9/1/2015)
Has the copy job ever run successfully on the new secondary
It runs successfully but takes a very long time, the restore job runs much longer as well, when compared for the 1st secondary.
I think I may have pinpointed the issue, the monitor instance for our configuration is on the 1st secondary, the 2nd secondary can't connect to the 1st secondary and therefore the monitor instance is not being updated with the last_copied_file etc.. for the 2nd secondary and I think this is causing the copy job on the 2nd secondary to scan the entire directory each time it runs.
Could this be the case?
September 2, 2015 at 5:32 am
SQLSACT (9/2/2015)
Perry Whittle (9/1/2015)
Has the copy job ever run successfully on the new secondaryIt runs successfully but takes a very long time, the restore job runs much longer as well, when compared for the 1st secondary.
I think I may have pinpointed the issue, the monitor instance for our configuration is on the 1st secondary, the 2nd secondary can't connect to the 1st secondary and therefore the monitor instance is not being updated with the last_copied_file etc.. for the 2nd secondary and I think this is causing the copy job on the 2nd secondary to scan the entire directory each time it runs.
Could this be the case?
Never had this exact scenario myself but sounds reasonable.
One thing I would reiterate is that if you use a central monitor server it must be available to all instances.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 2, 2015 at 5:37 am
Perry Whittle (9/2/2015)
SQLSACT (9/2/2015)
Perry Whittle (9/1/2015)
Has the copy job ever run successfully on the new secondaryIt runs successfully but takes a very long time, the restore job runs much longer as well, when compared for the 1st secondary.
I think I may have pinpointed the issue, the monitor instance for our configuration is on the 1st secondary, the 2nd secondary can't connect to the 1st secondary and therefore the monitor instance is not being updated with the last_copied_file etc.. for the 2nd secondary and I think this is causing the copy job on the 2nd secondary to scan the entire directory each time it runs.
Could this be the case?
Never had this exact scenario myself but sounds reasonable.
One thing I would reiterate is that if you use a central monitor server it must be available to all instances.
Thanks
We're in the process of making the monitor instance available to all servers involved in the config.
Hopefully that sorts this out.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply