Log shipping jobs not deleting old .sqb files in 6.3

astreet

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?

RBA

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.