What are the challenges you face when working across database platforms? Take the survey

Log shipping jobs not deleting old .sqb files in 6.3

astreetastreet Posts: 28
edited February 23, 2010 5:28AM in SQL Backup Previous Versions
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?


  • Options
    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


    <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.
    Robin Anderson
    Red-Gate Software
Sign In or Register to comment.