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

Issue with SQL Backup Pro jobs not honouring delete switch

JoeGTJoeGT Posts: 50
edited April 14, 2014 3:51PM in SQL Backup Previous Versions
In an environment using SQL Backup Pro to perform backups over 30+ instances and 180+ DBs. We have been using the product for almost 2 years now (various versions) and have not had any issues we were not able to resolve.

This one has me stumped though.

Nothing has changed in the setup of any of the jobs however in the last two weeks, ALL of the jobs have not been honouring the ERASFILES command. A sample of the one of the jobs is as below - all of them use the same command - ERASEFILES = xx

EXECUTE master..sqlbackup '-SQL "BACKUP LOGS [xxxxxx] TO DISK = ''xxxxx\<AUTO>.sqb''
MAILTO_ONERROR = ''xxxxxxxxx.com'',

The issue is that despite having this flag set and despite the jobs completing successfully (ie. the correct new back file being created), not all of the files that ought to meet the criteria for deletion are being removed.

A sample (with confidential details redacted) for one of the jobs shown in the example above :

SQL Backup log file


14/04/2014 7:30:00 AM: Backing up xxxxx (transaction log) on xxxxx instance to:
14/04/2014 7:30:00 AM: xxxxxxx.sqb

14/04/2014 7:30:00 AM: BACKUP LOG [xxxx] TO VIRTUAL_DEVICE = 'SQLBACKUP_2D00135A-E6AE-4EFD-9389-25EA23D8C06F', VIRTUAL_DEVICE = 'SQLBACKUP_2D00135A-E6AE-4EFD-9389-25EA23D8C06F01', VIRTUAL_DEVICE = 'SQLBACKUP_2D00135A-E6AE-4EFD-9389-25EA23D8C06F02',
VIRTUAL_DEVICE = 'SQLBACKUP_2D00135A-E6AE-4EFD-9389-25EA23D8C06F03' WITH BUFFERCOUNT = 24, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576, NAME = N'Database (xxxxx), 14/04/2014 7:30:00 AM', DESCRIPTION = N'Backup on 14/04/2014 7:30:00 AM Server:
xxxxxx Database: xxxxxx', FORMAT, CHECKSUM

14/04/2014 7:30:00 AM: Backup data size : 2.500 MB
14/04/2014 7:30:00 AM: Compressed data size: 20.000 KB
14/04/2014 7:30:00 AM: Compression rate : 99.22%

14/04/2014 7:30:00 AM: Processed 0 pages for database 'xxxxxx', file 'xxxxxx' on file 1.
14/04/2014 7:30:00 AM: BACKUP LOG successfully processed 0 pages in 0.193 seconds (0.000 MB/sec).
14/04/2014 7:30:04 AM: Deleting old backup file: xxxxxx.sqb
14/04/2014 7:30:04 AM: Deleting old backup file: xxxxxx.sqb

14/04/2014 7:35:16 AM: SQL Backup process ended.

14/04/2014 7:35:16 AM: Deleted msdb entries older than 16/10/2013 7:35:16 AM
14/04/2014 7:35:16 AM: Deleted local history entries older than 16/10/2013 7:35:16 AM

So you will note that is has removed some old files. However, if I look in the folder that holds these log backups, I can see that it has not removed all of the files that ought to have been removed. The circled files are a sample of those that should have been deleted.


So there is not a permissions issues - the account running the job is both able to create and delete files, no errors are appearing in log file for the jobs, and yet still, files are not being deleted.

This is causing us headaches for capacity management as we are being constantly pinged with various LUNs going over their thresholds and in some cases (where the backup files are very large) running out of space. With this many SQL instances and databases this is not something that any one of us on my team have bandwidth to be able to look at each day manually.

Can someone please help to explain why this is happening and let me know if any extra information that might help to identify what is going on here ?


  • Options
    peteypetey Posts: 2,358 New member
    Two possibilities I have come across when ERASEFILES doesn't work as expected:

    - the backup files password has changed, causing SQL Backup not to be able to read the header in the backup file

    - de-duplication is used, causing the file attributes to change and not meet SQL Backup's expected file attributes
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    Thanks Pete! The former will explain the situation for us - the encryption key was changed on these files a couple of weeks ago.

    Perhaps this is something that ought to be noted in the UI when changing/setting an encryption key ie. if you set or change the encryption key, you will manually need to delete any back up files that used the old key because of this issue of not being able to read the headers.

    And probably something in the log file that also points to the issue and thorws a warning ie. "Warning xxxx file was not able to be deleted as encryption key does match the key currently stored on the job"
Sign In or Register to comment.