Red Gate forums :: View topic - MOVETO option not deleting large log backup files
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Backup 6
SQL Backup 6 forum

MOVETO option not deleting large log backup files

Search in SQL Backup 6 forum
Post new topic   Reply to topic
Jump to:  
Author Message
meastland



Joined: 28 Mar 2011
Posts: 36

PostPosted: Sun Jul 03, 2011 4:50 pm    Post subject: MOVETO option not deleting large log backup files Reply with quote

I'm having an issue with my logshipping configuration on version 6.5. I have specified the MOVETO parameter to remove log files after they are restored, and this works very well most of the time.

However, I've seen issues recently with the MOVETO parameter related to large transaction log backup files. The files are being copied to the destination, but they are not being removed from the source. This causes the subsequent log shipping restore jobs to fail until the file is manually removed from the source directory.

I realize I could easily write a script to check for the existence of a given file in both the source and destination directories, but it seems like this would be handled by SQL Backup.

Thanks,

-Mike Eastland
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2301

PostPosted: Mon Jul 04, 2011 11:05 am    Post subject: Reply with quote

How big are the log files? Are there any warnings recorded in the SQL Backup log file when it failed to move those files?

Quote:
This causes the subsequent log shipping restore jobs to fail until the file is manually removed from the source directory.

By design, SQL Backup would attempt to restore those files, but if it fails because they have already been restored, and raised SQL error code 4326, SQL Backup will raise warning 470 and just move on to the next file. Is this recorded as happening in the SQL Backup log file?
_________________
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 7
Back to top
View user's profile Send private message Send e-mail
meastland



Joined: 28 Mar 2011
Posts: 36

PostPosted: Tue Jul 05, 2011 7:34 pm    Post subject: Reply with quote

Peter,

I have seen the issue occur on log backup files anywhere from 2GB to 6GB. In terms of logged errors, I am seeing the following:


From the initial backup log file:

7/2/2011 8:43:06 AM: Warning 130: MOVETO error: Failed to move file: <source_directory_and_file_path> (The system cannot find the file specified.) This path references a UNC share, but it is local to the server from which the backup is being executed.

7/2/2011 8:43:14 AM: Moved <source_file> to <destination_directory>. The file is getting to the destination directory at some point, as indicated by subsequest error messages.


From the next log restore attempt:

7/2/2011 10:10:19 AM: Warning 170: Log files are not in sequence:

7/2/2011 10:10:20 AM: SQL Server error

7/2/2011 10:10:20 AM: SQL error 3013: SQL error 3013: RESTORE LOG is terminating abnormally.

7/2/2011 10:10:20 AM: SQL error 4326: SQL error 4326: The log in this backup set terminates at LSN 959142000003918900001, which is too early to apply to the database. A more recent log backup that includes LSN 959142000004032400001 can be restored.

7/2/2011 10:10:20 AM: Warning 130: MOVETO error: Failed to move file: <source_directory_and_file_path> (The file exists.)


From subsequent log restore attempts until problem file is manually removed from source directory:

7/2/2011 12:10:22 PM: Warning 170: Log files are not in sequence:

7/2/2011 12:10:23 PM: SQL Server error

7/2/2011 12:10:23 PM: SQL error 3013: SQL error 3013: RESTORE LOG is terminating abnormally.

7/2/2011 12:10:23 PM: SQL error 4326: SQL error 4326: The log in this backup set terminates at LSN 959142000003918900001, which is too early to apply to the database. A more recent log backup that includes LSN 959142000004032400001 can be restored.

7/2/2011 12:10:23 PM: Warning 470: Transaction log has already been restored.

7/2/2011 12:10:23 PM: Warning 130: MOVETO error: Failed to move file: <source_directory_and_file_path> (The file exists.)

7/2/2011 12:10:23 PM: Restoring Newsletter (transaction logs) from:

7/2/2011 12:10:23 PM: SQL Server error

7/2/2011 12:10:23 PM: SQL error 3013: SQL error 3013: RESTORE LOG is terminating abnormally.

7/2/2011 12:10:23 PM: SQL error 4305: SQL error 4305: The log in this backup set begins at LSN 959143000007452500001, which is too recent to apply to the database. An earlier log backup that includes LSN 959142000004032400001 can be restored.

7/2/2011 12:10:23 PM: Mail sent successfully to: <notification_email_address>


It appears that each of the error and/or warning codes you referenced are represented in the error log chain. I guess my next question is, do I need to look to further customize the log shipping implementation to handle this condition?

Thanks again for your time.

-Mike
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2301

PostPosted: Wed Jul 06, 2011 1:54 am    Post subject: Reply with quote

Could you please send the 3 referenced log files to me via e-mail (peter.yeoh at red-gate.com)? It would make understanding the sequence of events easier.

Thank you.
_________________
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 7
Back to top
View user's profile Send private message Send e-mail
meastland



Joined: 28 Mar 2011
Posts: 36

PostPosted: Wed Jul 18, 2012 10:42 pm    Post subject: Can we skip "early" log files? Reply with quote

Petey,

If this functionality already exists within the tool, please let me know.

Would it be beneficial to add an optional parameter to the RESTORE LOG command within SQL Backup that would instruct the application to "skip" over any files that return error 4326 (SQL error 4326: SQL error 4326: The log in this backup set terminates at LSN xxx, which is too early to apply to the database. A more recent log backup that includes LSN xxx can be restored.)? Perhaps it could work something like a LATEST_* clause for logs by determining which logs can be restored. I've written custom code around SQL Backup to accomplish this in the past, but it seems like it would be a powerful option to include.

Thanks,

-Mike
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2301

PostPosted: Thu Jul 19, 2012 7:18 am    Post subject: Reply with quote

Presently, when SQL Backup encounters SQL error 4326, it raises warning code 470, moves the file to the MOVETO location (if used), and continues with the next transaction log backup file.

This feature existed since version 6.3.
_________________
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 7
Back to top
View user's profile Send private message Send e-mail
meastland



Joined: 28 Mar 2011
Posts: 36

PostPosted: Thu Jul 19, 2012 4:16 pm    Post subject: Reply with quote

Petey,

Thanks for the quick response. Are there any plans to allow this error to be skipped without requiring the MOVETO parameter?

Thanks,

-Mike
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2301

PostPosted: Thu Jul 19, 2012 5:32 pm    Post subject: Reply with quote

I see what you mean now, SQL Backup will only ignore the error if the MOVETO parameter is used. Our assumption was that suppressing the error when the log files have already been restored was only useful in log shipping scenarios, where the MOVETO parameter is almost always used.

We would like to understand your requirements. Is there a reason why you don't use the MOVETO parameter? Why do you leave restored log files in the same folder as log files that have yet to be restored?

Thank you.
_________________
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 7
Back to top
View user's profile Send private message Send e-mail
meastland



Joined: 28 Mar 2011
Posts: 36

PostPosted: Thu Jul 19, 2012 5:45 pm    Post subject: Reply with quote

Peter,

My reasoning for wanting to skip the 4326 error without the MOVE command is when we have multiple standby databases restoring log backups from the same primary. If the files are in the same network location and the restore commands can be directed to skip 4326 errors, I can point the restore jobs for both standby databases to the same directory. However, if I have to move the files, then it complicates matters for any standby database other than the first, in addition to not keeping the files in the expected location for a possible production restore / recovery.

Thanks,

-Mike
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2301

PostPosted: Tue Jul 31, 2012 9:14 am    Post subject: Reply with quote

I've raised a design enhancement request for this (ref. SB-4326).
_________________
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 7
Back to top
View user's profile Send private message Send e-mail
jeffn



Joined: 07 May 2012
Posts: 1
Location: United States

PostPosted: Tue Nov 20, 2012 3:48 pm    Post subject: Reply with quote

Any update on this. It would be helpful for my environment.

My setup:
Primary SQL Server
Local LogShip Standby SQL Server
Remote LogShip Standby SQL Server

If I use the Mirror Option to copy the files to both Standby Servers:
If one of the machines is inaccessible briefly then the server will be missing the log files. Obviously I could write something to make the copy happen again but it would have to be smart enough to know whether the .trn had been applied and moved to the post restore location or if it was never copied to the pre restore location.

If I use the CopyTo option to copy the file to a second location and the CopyTo server is inaccessible briefly then the LogCopy tasks will start to backup. If the first server applies the log and then moves it to another directory the Log Copy task will fail as it cannot find the source file.

I would like to use the second option and just have the restore task be smart enough to only attempt the log backups which have not been restored, and not move them to a post restore location.

I have found a workaround although I don't use it as it causes the jobs to run much longer. You can use the MoveTo command on the log restore and just point the MoveTo location to the source location. In effect you don't move the files and the log restore eats the 4326 error. I don't know if this has ill effects since I don't use it but I just wanted to make others aware of it as a possible solution if you aren't worried about the restore log taking more time.
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2301

PostPosted: Wed Nov 21, 2012 2:32 am    Post subject: Reply with quote

Sorry, that should have been SB-5335. No decision has been made yet with regards to the implementation of this change.
_________________
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 7
Back to top
View user's profile Send private message Send e-mail
petey



Joined: 24 Apr 2005
Posts: 2301

PostPosted: Wed Nov 21, 2012 9:48 am    Post subject: Reply with quote

There is a patch build, 7.2.2.8, that skips the actual moving process when the source and target file names are identical. This is presently the least-impact workaround for this issue, taking advantage of the workaround jeffn mentioned. Thus, if you want SQL Backup to proceed to the next backup set when an earlier backup set has been restored, but you don't want to move the trx log backup files out from the present locations, use the MOVETO option with the same folder name as the original files e.g.

Code:
EXEC master..sqlbackup '-sql "RESTORE LOG AdventureWorks FROM DISK = [g:\backups\AdventureWorks_log_*.sqb] WITH STANDBY = [g:\standby\AdventureWorks.und], MOVETO = [g:\backups\]"'


You can download this patch from

ftp://support.red-gate.com/patches/sql_backup/SQB_7_2_2_8.zip

Please note that this patch release has not been as extensively tested as a regular release, so use at your own risk.
_________________
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 7
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group