Warning Deleting Old Files

appwrightappwright Posts: 62
edited May 10, 2005 10:11PM in SQL Backup Previous Versions
I have been talking with you via support email however repsonse has been sporadic. I am getting errors now even when the backup utility is trying to delete the old backups on the local hard drive. Here is my error log:

4/27/2005 11:03:24 PM: SQL Backup 3.1.0, (c) Red Gate Software Ltd 2004-2005
4/27/2005 11:03:24 PM:
4/27/2005 11:03:24 PM: Backing up TFI (full database) to
D:\@Backups\PROD01_TFI_db_(full)_200504272303.sqb ...
4/27/2005 11:03:24 PM:
4/27/2005 11:03:24 PM: BACKUP DATABASE [TFI] TO DISK =
'D:\@Backups\PROD01_TFI_db_(full)_200504272303.sqb' WITH NAME =
'Database
(TFI) Full', DESCRIPTION = '', PASSWORD = 'XXXXXXXXXX', ERASEFILES = 1,
MAILTO = 'xxx', COMPRESSION = 3
4/27/2005 11:03:24 PM:
4/27/2005 11:03:24 PM: osql -S AWIPROD_01 -Q "BACKUP DATABASE [TFI] TO
VIRTUAL_DEVICE = 'SQLBACKUP_71519341' WITH BLOCKSIZE = 65536,
MAXTRANSFERSIZE = 1048576, NAME = 'Database (TFI) Full', DESCRIPTION = '', FORMAT" -b -w 120 -U sa -P XXXXXXXXXX
4/27/2005 11:03:25 PM: Backup data size : 20.063 MB
4/27/2005 11:03:25 PM: Compressed data size: 3.871 MB
4/27/2005 11:03:25 PM: Compression rate : 80.71%
4/27/2005 11:03:27 PM: Process completed successfully.
4/27/2005 11:03:27 PM:
4/27/2005 11:03:27 PM: Deleting old backup file: D:\@Backups\PROD01_TFI_db_(full)_200504261026.sqb
4/27/2005 11:03:27 PM: Warning 161: ERASEFILES error: Failed to delete file: D:\@Backups\PROD01_TFI_db_(full)_200504261026.sqb. Error code: 158 (The segment is already unlocked).

Comments

  • peteypetey Posts: 2,358 New member
    Was the file deleted?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Does a file with the same name already exist in the folder? Maybe it would help to use the INIT option, which overwrites existing files with the same name, or specifying ERASEFILES_ATSTART to delete the files before running the backup.
  • No the file was not deleted. And the file does exist. It's just not getting deleted.
  • I was looking this over. If the filename is not the same will it not delete the old files. I am using the script posted in this forum to delete all backups. This gives each file a unique name. Could that be the problem?
  • peteypetey Posts: 2,358 New member
    The file names need not be the same for the ERASEFILES or ERASEFILES_ATSTART options to work. Is it possible that the old file is in use by another application at the time that SQL Backup tries to delete it e.g. virus scanners, batch copy jobs etc?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • petey wrote:
    The file names need not be the same for the ERASEFILES or ERASEFILES_ATSTART options to work. Is it possible that the old file is in use by another application at the time that SQL Backup tries to delete it e.g. virus scanners, batch copy jobs etc?

    Nope - nothing would be touching those files. It happens when I run it from the command line also.
  • peteypetey Posts: 2,358 New member
    Could you pls list out the file attributes of the backup file that is not being deleted, as displayed in Windows Explorer? Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Attributes? I guess you mean ReadOnly and Hidden. Both are unchecked. File size is 7.02MB, the one that failed today we created on May 03 2005. File name is: TAHOE_DEV2_db_(full)_200505032206.sqb.

    Here is the warning on it:

    5/5/2005 10:06:31 PM: SQL Backup 3.1.0, (c) Red Gate Software Ltd 2004 - 2005
    5/5/2005 10:06:31 PM:
    5/5/2005 10:06:31 PM: Backing up DEV2 (full database) on TAHOE instance to D:\Backups\TAHOE_DEV2_db_(full)_200505052206.sqb ...
    5/5/2005 10:06:31 PM:
    5/5/2005 10:06:31 PM: BACKUP DATABASE [DEV2] TO DISK = 'D:\Backups\TAHOE_DEV2_db_(full)_200505052206.sqb' WITH NAME = 'Database (DEV2) Full 200505052206', DESCRIPTION = '', INIT, PASSWORD = 'XXXXXXXXXX', ERASEFILES_ATSTART = 2, MAILTO = 'porter@2morrow.com', COMPRESSION = 3
    5/5/2005 10:06:31 PM:
    5/5/2005 10:06:31 PM: Deleting old backup file: D:\Backups\TAHOE_DEV2_db_(full)_200505022206.sqb
    5/5/2005 10:06:31 PM: Warning 161: ERASEFILES error: Failed to delete file: D:\Backups\TAHOE_DEV2_db_(full)_200505022206.sqb. Error code: 158 (The segment is already unlocked).
    5/5/2005 10:06:32 PM: osql -S TAHOE\TAHOE -Q "BACKUP DATABASE [DEV2] TO VIRTUAL_DEVICE = 'SQLBACKUP_207628493' WITH BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576, NAME = 'Database (DEV2) Full 200505052206', DESCRIPTION = '', FORMAT" -b -w 120 -U sa -P XXXXXXXXXX
    5/5/2005 10:06:35 PM: Backup data size : 25.500 MB
    5/5/2005 10:06:35 PM: Compressed data size: 7.024 MB
    5/5/2005 10:06:35 PM: Compression rate : 72.45%
    5/5/2005 10:06:38 PM: Process completed successfully.
    5/5/2005 10:06:38 PM:
  • BTW: I'm using the script that was posting in the forum to backup all databases. Is there anything wrong with that script?
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hello Todd,

    There are a few more attributes than read-only and hidden. I think that Peter's concerned because there is some code in SQL Backup that checks all of these attributes because you can tell the ERASEFILES option not to erase files with a certain attribute set. I've written a bit of script that can help. If you copy and paste this script into notepad, then save it to a file and run it, it will report all of the attributes set on a file:

    cscript fileattrs.js /file:<name of file>

    Here is the code:
    /* script to list all possible file attributes */
    /* 2005 Brian Donahue Red Gate Software Ltd */
    /* Requires WScript 5.6 &#40;2000/XP&#41;*/
    
    var filename = WScript.Arguments.Named.Item&#40;"file"&#41;;
    
    var FSO = new ActiveXObject&#40;"Scripting.FileSystemObject"&#41;;
    var obFile=FSO.GetFile&#40;filename&#41;;
    
    var aNone=0;
    var aReadOnly=1;
    var aHidden=2;
    var aSystem=4;
    var aVolume=8;
    var aDirectory=16;
    var aArchive=32;
    var aAlias=64;
    var aCompressed=128;
    
    var output = "attributes of "+filename +"\r\n";
    if &#40;obFile.Attributes==aNone&#41; output += "There are no attributes set";
    else &#123;
    	if &#40;aReadOnly==&#40;obFile.Attributes & aReadOnly&#41;&#41; output += "Read Only\r\n";
    	if &#40;aHidden==&#40;obFile.Attributes & aHidden&#41;&#41; output += "Hidden\r\n";
    	if &#40;aSystem==&#40;obFile.Attributes & aSystem&#41;&#41; output += "System\r\n";
    	if &#40;aVolume==&#40;obFile.Attributes & aVolume&#41;&#41; output += "Volume\r\n";
    	if &#40;aDirectory==&#40;obFile.Attributes & aDirectory&#41;&#41; output += "Directory\r\n";
    	if &#40;aArchive==&#40;obFile.Attributes & aArchive&#41;&#41; output += "Archive\r\n";
    	if &#40;aAlias==&#40;obFile.Attributes & aAlias&#41;&#41; output += "Alias\r\n";
    	if &#40;aCompressed==&#40;obFile.Attributes & aCompressed&#41;&#41; output += "Compressed\r\n";
    	
    &#125;
    WScript.Echo&#40;output&#41;;
    
  • Here is the output for that file:

    D:\Backups>cscript fileattrib.js /file:TAHOE_DEV2_db_(full)_200505032206.sqb
    Microsoft (R) Windows Script Host Version 5.6
    Copyright (C) Microsoft Corporation 1996-2001. All rights reserved.

    attributes of TAHOE_DEV2_db_(full)_200505032206.sqb
    Archive
  • I have some new information:

    I noticed that it is actually deleting older files, however it is saying it is not. Any ideas?
  • peteypetey Posts: 2,358 New member
    I have encountered the same situation where the files are deleted but the warning you described is raised, due to a quirk in the file deleting function that's used. That was why my original response to your issue was to check if the file(s) were actually deleted.

    In 3.2, a check is actually made if the physical file exists, before a warning is raised.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Can I get 3.2?

    I also noticed that even if I have the deletion set for 1, I will still end up with 2 files in holding instead of just the current backup.
  • peteypetey Posts: 2,358 New member
    >> I also noticed that even if I have the deletion set for 1, I will still end up with 2 files in holding instead of just the current backup.

    Check the timestamp of the two files. SQB will only delete the older file if it is >= 24 hours older than the timestamp of the current backup file.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • The new version 3.2 has solved the Warning problem. No more file delete warnings!
Sign In or Register to comment.