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

Processed log files not being deleted

cameron.gibbscameron.gibbs Posts: 18
edited October 8, 2008 8:23PM in SQL Backup Previous Versions
When log shipping was setup, I set the option to delete processed logship files after 7 days (168 hours), but the files are not being deleted as they should be - the script for the job is below.

Any ideas ?

DECLARE @errorcode INT
DECLARE @sqlerrorcode INT
EXECUTE master..sqlbackup N'-SQL "RESTORE LOG [DR_DecisionPoint] FROM DISK = ''\\SCRATCHY\LogShip\LOG_DecisionPoint_*.sqb'' WITH MAILTO_ONERROR = ''xxx@xxx.xxx'', ERASEFILES = 168h, STANDBY = ''D:\LogShip\Undo\UNDO_DR_DecisionPoint.dat'', MOVETO = ''D:\LogShip\Processed''"', @errorcode OUT, @sqlerrorcode OUT;
IF (@errorcode >= 500) OR (@sqlerrorcode <> 0)
BEGIN
RAISERROR ('SQL Backup failed with exit code: %d SQL error code: %d', 16, 1, @errorcode, @sqlerrorcode)
END

Comments

  • Options
    Eddie DEddie D Posts: 1,793 Rose Gold 5
    Thank you for your post into the forum.

    I suspect that you need to add the FILEOPTIONS argument to your script.

    Unfortunately you cannot configure the FILEOPTIONS argument using the SQL Backup GUI, you must do this manually. To delete old backup files from the processed folder you need to modify the job script in Enterprise Manager / SQL Server Management Studio ->SQL Server Agent ->Jobs and add the FILEOPTIONS Argument.

    Using your restore script, I have modified the script to include the FILEOPTIONS argument:

    DECLARE @errorcode INT
    DECLARE @sqlerrorcode INT
    EXECUTE master..sqlbackup N'-SQL "RESTORE LOG [DR_DecisionPoint] FROM DISK = ''\\SCRATCHY\LogShip\LOG_DecisionPoint_*.sqb'' WITH MAILTO_ONERROR = ''xxx@xxx.xxx'', ERASEFILES = 168h, FILEOPTIONS = 1, STANDBY = ''D:\LogShip\Undo\UNDO_DR_DecisionPoint.dat'', MOVETO = ''D:\LogShip\Processed''"', @errorcode OUT, @sqlerrorcode OUT;
    IF (@errorcode >= 500) OR (@sqlerrorcode <> 0)
    BEGIN
    RAISERROR ('SQL Backup failed with exit code: %d SQL error code: %d', 16, 1, @errorcode, @sqlerrorcode)
    END

    Further advice on the FILEOPTIONS argument can be found in the SQL Backup Help File ->The SQL Backup Toolkit ->Toolkit Syntax ->The Restore Command and in The Backup Command sections.

    Many Thanks
    Eddie
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
  • Options
    Thanks Eddie,
    I'll give that a go and see what happens.

    Can you perhaps explain to me why the SQL Backup GUI does not add this option in ? Having a setting in the GUI for removing the old files that doesn't actually remove the files seems a bit pointless.

    Cameron
Sign In or Register to comment.