Logshipping, the backup already exists destination

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Logshipping, the backup already exists destination

Author

Topic

ranvir_2k
Posting Yak Master

180 Posts

Posted - 2014-10-08 : 07:32:36

Hi all,

I've recently set up log shipping but I'm a bit concerned with some of the messages I'm seeing in the copy job.

I see multiple mesages like this:

The backup file '\\myserver\logfolder\database_20141008024522.trn' already exists in the destination directory.

This copy job seems to be checking the entire source location and seeing if the file exists on the destination. This is quite time consuming.

Is this normal behavior? Is there any way to change this?

I would have expected SQL Server to know what file to copy next, not scan every log in the source folder.

Shanky
Yak Posting Veteran

84 Posts

Posted - 2014-10-08 : 09:28:48

What is current status of Log shipping how is backup and restore job doing is it actually restoring log backup can you please check. From message it seems that backup job is not producing new log files for whatever may be the reason and when copy job is going for copy its finding the same log file which it has already copied to fileshare.

Hope this helps

Regards
Shanky
SQL Server MVP
http://social.technet.microsoft.com/wiki/contents/articles/24253.list-of-articles-by-shanky.aspx

ranvir_2k
Posting Yak Master

180 Posts

Posted - 2014-10-08 : 10:36:01

Yes I can see that the logshipping is working but it is taking so long due to the fact that it's scanning the entire directory before knowing what file to copy.

I have found what the issue is - I can see that when I run sp_help_log_shipping_monitor on the destination, the fields 'last_copied_file' and 'last_restored_file' are NULL

If these fields were not NULL and contained the correct data then the job would know what the last file copied was.

How can I fix this?

Shanky
Yak Posting Veteran

84 Posts

Posted - 2014-10-08 : 18:57:53

Hi,
What does this return
select last_copied_file,last_copied_date,last_restored_file,last_restored_date
from msdb.dbo.log_shipping_monitor_secondary

Hope this helps

Regards
Shanky
SQL Server MVP
http://social.technet.microsoft.com/wiki/contents/articles/24253.list-of-articles-by-shanky.aspx

ranvir_2k
Posting Yak Master

180 Posts

Posted - 2014-10-09 : 06:53:30

This log shipping configuration is using the log shipping monitor. This is what is causing the issue

We have three sites:
Site A - The Primary site
Site B - The Secondary site (DR), log shipping monitor also resides here (along with the LS Alert Job)
Site C - A reporting site that also restores the logs

On site B everything is fine, because that is where the monitoring server is. It is showing the correct last_copied_file and last_restored_file.

On Site C the last_copied_file and last_restored_file are NULL due to the fact that the monitoring server is on site B.

Is there any way to fix this? Or do I need to remove the monitoring server?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-09 : 11:20:55

I don't understand the purpose of Site C restoring the logs.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

ranvir_2k
Posting Yak Master

180 Posts

Posted - 2014-10-09 : 11:47:04

Site C is used for reporting purposes.

The network between site A and C is not that fast and the database is very big, even compressed it takes a long time to copy. The version of SQL Server isn't enterprise so we can't use Mirroring and Snapshots. We don't want to use replication either as the team at Site A does not have any experience of managing this.

We copy the logs throughout the whole day and restore during the night, during the day the database is in Standy/Read-Only mode.

Anyway, is there any solution to this apart from setting up log shipping from scratch without the monitor?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-09 : 12:08:50

Why can't the secondary server be the one to restore the logs? I guess I'm confused on your configuration. Do you have two secondary servers?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

ranvir_2k
Posting Yak Master

180 Posts

Posted - 2014-10-10 : 05:26:41

Yes there are two secondary servers, anyway I believe the only solution is to set up log shipping again without this monitor.

ranvir_2k
Posting Yak Master

180 Posts

Posted - 2014-10-10 : 06:27:51

What is the log ship monitor used for?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-10 : 12:38:09

It's used for the monitor job.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-10 : 12:38:44

You don't need to start from scratch with log shipping since your secondary servers already have the databases in the correct mode. Just tell log shipping to start from there and not from a full backup.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

ranvir_2k
Posting Yak Master

180 Posts

Posted - 2014-10-12 : 17:29:31

quote:
Originally posted by tkizer

It's used for the monitor job.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Do you mean the Alert job?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-13 : 11:46:57

quote:
Originally posted by ranvir_2k

quote:
Originally posted by tkizer

It's used for the monitor job.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Do you mean the Alert job?

Yes the job that alerts you of issues.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources