ERASEFILES_ATSTART fails to delete all backup files

stratburststratburst Posts: 7
edited November 28, 2005 8:41PM in SQL Backup Previous Versions
I am performing transaction log file backups via a scheduled stored procedure job utilizing ERASEFILES_ATSTART. The job successfully backs up all logs but fails to erase all of the transaction log backup files as configured. All databases configured with full recovery model. No error seem to be logged. The following is a snippit of the SP that sets the log backup parameters. Please advise.

set @backupstring = '-SQL "BACKUP LOG TO DISK = ''' + @filename + ''' WITH NAME = ''Database (' + @databasename + ')'', VERIFY, ERASEFILES_ATSTART = 1, MAILTO_ONERROR = ''[email protected]'', COMPRESSION = 1" -E'

Comments

  • Hi

    The ERASEFILES_ATSTART and ERASEFILES options value is number of days. This is calculated as 24 hour blocks, so the only files deleted will be greater than X*24 hours old.

    In your script all the files that remained should be less than 24 hours old.

    Regards
    Dan
    Daniel Handley
    Red Gate Software Ltd
  • The server has multiple databases, the SP backs up TRN log files every 2 hours and deletes log backup files older than one day on all databases as configured, except for a few. The backup files that are not being deleted go back to the date that the SQLBackup job was created.

    Any known issues with long file names? This might be entirely irrelevant but it seems that the backup files that are not being deleted are 20+ char long. Doesn't seem to be a 15+1 char issue as several of the databases are longer that 16 char but less than 20.
  • Hi

    We have had no reports of long file name problems. Can you check that the SQLAgent user has adequate rights to delete files from the directory, and that you are running version 3.2 .

    Regards
    Dan
    Daniel Handley
    Red Gate Software Ltd
  • I have run some tests and have verified that this appears as a bug in SQLBackup. Erasefiles will not remove files with database names that are longer than 22 charactors. Resolution: export database with long name to database with name less than 22 charactors. Erasefiles will then work as expected.
  • peteypetey Posts: 2,358 New member
    Thx for reporting the bug.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • peteypetey Posts: 2,358 New member
    The problem is that database names are truncated to 22 characters in the SQL Backup file header block. For ERASEFILES to work, it needs to find an exact match for the database name.

    This will be fixed in a future release. If you need this to work now on English operating systems, you can do the following, but note that this is an unofficial, unsupported workaround and has not been fully tested:

    Using a hex editor, look for the following sequence in sqlbackup.exe and sqlbackupc.exe:

    B916000000BA01000000

    Change the value 16 (hex for 22) to 2C (hex for 44). You should find two occurences of this sequence. The first is for the instance name, and the second for the database name. Making both changes will then store the first 44 characters of the instance/database name in the SQL Backup file header block.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Has this been fixed in the latest version of SQLBackup?
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    Hi Mel,

    Sorry, but we haven't released a new version since the last post on 19 June. I'm sure that the fix will be included in the next release.
  • Has this been fixed in the latest version of SQLBackup yet...?
  • peteypetey Posts: 2,358 New member
    This will not be changed in version 3 as on non-English systems, the 44 bytes need to cater for multi-byte character sets.

    In version 4, the full name of the database (max 128 characters) will be stored as a unicode string.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
This discussion has been closed.