Issue with SQL Backup Pro jobs not honouring delete switch
JoeGT
Posts: 50
In an environment using SQL Backup Pro 7.6.0.26 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 =
EXECUTE master..sqlbackup '-SQL "BACKUP LOGS [xxxxxx] TO DISK = ''xxxxx\<AUTO>.sqb''
WITH ERASEFILES = 5,
MAILTO_ONERROR = ''xxxxxxxxx.com'',
PASSWORD = ''<ENCRYPTEDPASSWORD></ENCRYPTEDPASSWORD>'',
CHECKSUM, DISKRETRYINTERVAL = 30, DISKRETRYCOUNT = 10, COMPRESSION = 4, KEYSIZE = 256, THREADCOUNT = 4"'
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 7.6.0.29
PROCESSES COMPLETED SUCCESSFULLY
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 ?
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 =
EXECUTE master..sqlbackup '-SQL "BACKUP LOGS [xxxxxx] TO DISK = ''xxxxx\<AUTO>.sqb''
WITH ERASEFILES = 5,
MAILTO_ONERROR = ''xxxxxxxxx.com'',
PASSWORD = ''<ENCRYPTEDPASSWORD></ENCRYPTEDPASSWORD>'',
CHECKSUM, DISKRETRYINTERVAL = 30, DISKRETRYCOUNT = 10, COMPRESSION = 4, KEYSIZE = 256, THREADCOUNT = 4"'
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 7.6.0.29
PROCESSES COMPLETED SUCCESSFULLY
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 ?
Comments
- 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
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
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"