Processed log files not being deleted

cameron.gibbs

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

Eddie D

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

cameron.gibbs

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