Log shipping jobs are not deleting old .sqb files from the log shipping share using FILEOPTIONS = 3. My restore log command has Erasefiles = 4 and Fileoptions =3 which should delete files older that 4 days. All of my od log files have been applied to my standby database. If I set the Fileoptions=1 the deletion of old files works correctly. Any idea to this issue?
Comments
1 comment
-
Hi astreet,
For the backup job step, FILEOPTIONS 3 should have the same effect as FILEOPTIONS 1 on the COPYTO directory. I have raised this as a bug and will look into this.
However, if you created the log shipping jobs via the GUI then the restore job step will contain a MOVETO command, which takes processed files and moves them to another directory out of reach of the backup job's erase files functionality.
As a temporary workaround you can create an additional job step to perform the file purge. The following syntax may be of use:EXEC master..sqbutility 1032, <database name>, <path>, <backup type>, <retention value>, <password>, @count OUT, @error OUT
where
<path> is the list of paths to delete files from. Multiple paths should be separated by |
<backup type> is either D (full), I (differential) or L (log)
<retention value> is as per ERASEFILES syntax i.e. a numerical value indicates days to keep, 2h = 2 hours, 2b = 2 backup sets
@count is the number of files deleted
@error is the error message (if any)
E.g. syntax:
DECLARE @count int
DECLARE @error varchar(1024)
EXEC master..sqbutility 1032, 'pubs', 'e:\temp\test', 'D', '5b', 'turtwig', @count OUT, @error OUT
SELECT @count, @error
NOTE: Even though the backup sets may not be encrypted, if you want the @count and @error return values, you must pass an empty string for the password parameter.
Add comment
Please sign in to leave a comment.