MOVETO option not deleting large log backup files

meastland · July 3, 2011 11:50AM

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

petey · July 4, 2011 6:05AM

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

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?

meastland · July 5, 2011 2:34PM

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

petey · July 5, 2011 8:54PM

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.

meastland · July 18, 2012 5:42PM

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

petey · July 19, 2012 2:18AM

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.

meastland · July 19, 2012 11:16AM

Petey,

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

Thanks,

-Mike

petey · July 19, 2012 12:32PM

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.

meastland · July 19, 2012 12:45PM

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

petey · July 31, 2012 4:14AM

I've raised a design enhancement request for this (ref. SB-4326).

jeffn · November 20, 2012 9:48AM

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.

petey · November 20, 2012 8:32PM

Sorry, that should have been SB-5335. No decision has been made yet with regards to the implementation of this change.

petey · November 21, 2012 3:48AM

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.

EXEC master..sqlbackup '-sql "RESTORE LOG AdventureWorks FROM DISK = &#91;g:\backups\AdventureWorks_log_*.sqb&#93; WITH STANDBY = &#91;g:\standby\AdventureWorks.und&#93;, MOVETO = &#91;g:\backups\&#93;"'

You can download this patch from

ftp://support.red-gate.com/patches/sql_ ... _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.

MOVETO option not deleting large log backup files

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk