ERASEFILES on remote server fails for just 1 DB

PDinCAPDinCA Posts: 642 Silver 1
edited January 30, 2014 4:19PM in SQL Backup Previous Versions
Server Version 7.6.0.29 but has been having issues for many months on prior 7.x versions.

I have 8 databases including the system and 2 SSRS DBs.

Two of them are copied over the network to the exact same folder on the backup file server.

They both copy the full and tranlog backups, full to folder-1, tranlog to folder-2, both 1st-level folders on the FS. No subfolders are involved.

I have to periodically stop the SQL Backup service on the production instance (not a great plan!) in order to release the "file is being used by another process" lock on file deletion. As space is scant, I have to keep tabs on this. Having last week, as in under 10 days ago, manually deleted files that are months older than the 15-day retention (remote), I reviewed the jobs in detail today only to find that the largest database's tranlog backup jobs are "Successful (with warnings)". Those warnings are ALL concerning the kind of message shown below:
SQL Backup log file 7.6.0.29
 
-SQL "BACKUP LOG [MyDatabase] TO DISK = 'E:\DB_Backups\<database>\<AUTO>.sqb' WITH ERASEFILES = 2, ERASEFILES_REMOTE = 15, FILEOPTIONS = 4, CHECKSUM, DISKRETRYINTERVAL = 30, DISKRETRYCOUNT = 10, COMPRESSION = 4, COPYTO = '\\FileServer\MYSERVER_logs', 
 INIT, THREADCOUNT = 6, VERIFY "
 
-----------------------------  ERRORS AND WARNINGS -----------------------------
 
 
12/19/2013 12:01:05 PM: Deleting old backup file: E:\DB_Backups\MyDatabase\LOG_MYSERVER_MyDatabase_20131217_114600.sqb
12/19/2013 12:01:05 PM: Deleting old backup file: E:\DB_Backups\MyDatabase\LOG_MYSERVER_MyDatabase_20131217_120100.sqb
12/19/2013 12:01:05 PM: Deleting old backup file: \\FileServer\MYSERVER_logs\LOG_MYSERVER_MyDatabase_20131204_051600.sqb
12/19/2013 12:01:06 PM: Warning 161: ERASEFILES error: Failed to delete file: \\FileServer\MYSERVER_logs\LOG_MYSERVER_MyDatabase_20131204_051600.sqb.  Error code: 32 (The process cannot access the file because it is being used by another process).
12/19/2013 12:01:06 PM: Deleting old backup file: \\FileServer\MYSERVER_logs\LOG_MYSERVER_MyDatabase_20131204_053100.sqb
12/19/2013 12:01:07 PM: Warning 161: ERASEFILES error: Failed to delete file: \\FileServer\MYSERVER_logs\LOG_MYSERVER_MyDatabase_20131204_053100.sqb.  Error code: 32 (The process cannot access the file because it is being used by another process).
12/19/2013 12:01:07 PM: Deleting old backup file: \\FileServer\MYSERVER_logs\LOG_MYSERVER_MyDatabase_20131204_054600.sqb
12/19/2013 12:01:09 PM: Warning 161: ERASEFILES error: Failed to delete file: \\FileServer\MYSERVER_logs\LOG_MYSERVER_MyDatabase_20131204_054600.sqb.  Error code: 32 (The process cannot access the file because it is being used by another process).
12/19/2013 12:01:09 PM: Deleting old backup file: \\FileServer\MYSERVER_logs\LOG_MYSERVER_MyDatabase_20131204_060100.sqb
12/19/2013 12:01:10 PM: Warning 161: ERASEFILES error: Failed to delete file: \\FileServer\MYSERVER_logs\LOG_MYSERVER_MyDatabase_20131204_060100.sqb.  Error code: 32 (The process cannot access the file because it is being used by another process).
12/19/2013 12:01:10 PM: Deleting old backup file: \\FileServer\MYSERVER_logs\LOG_MYSERVER_MyDatabase_20131204_113100.sqb
12/19/2013 12:01:11 PM: Warning 161: ERASEFILES error: Failed to delete file: \\FileServer\MYSERVER_logs\LOG_MYSERVER_MyDatabase_20131204_113100.sqb.  Error code: 32 (The process cannot access the file because it is being used by another process).
12/19/2013 12:01:11 PM: Deleting old backup file: \\FileServer\MYSERVER_logs\LOG_MYSERVER_MyDatabase_20131204_114600.sqb
Diagnosis, please.
Jesus Christ: Lunatic, liar or Lord?
Decide wisely...

Comments

  • peteypetey Posts: 2,358 New member
    The next time this happens, could you please run

    EXEC master..sqbutility 9997

    to generate a stack trace file, and send me the file ([email protected])? This file is named SQBCoreService_<instance name>_bugreport.txt, and is located in 'C:\Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Log\' on Windows 2003 and older, and 'C:\ProgramData\Red Gate\SQL Backup\Log\' on Windows Vista and newer.

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Eddie DEddie D Posts: 1,641 Rose Gold 5
    Hi

    Another quick thing to check for, if you have an anti-virus application that performs an on-access or real-time check of a file when it is opened to read, edited or being created. This may prevent SQL Backup reading the backup file header and deleting the backup file if it meets the criteria for deletion.

    Many Thanks
    Eddie
    Eddie Davis
    Product Support Engineer
    Redgate Software Ltd
    Email: [email protected]
  • PDinCAPDinCA Posts: 642 Silver 1
    Hi

    Another quick thing to check for, if you have an anti-virus application that performs an on-access or real-time check of a file when it is opened to read, edited or being created. This may prevent SQL Backup reading the backup file header and deleting the backup file if it meets the criteria for deletion.

    Many Thanks
    Eddie
    The odd thing, Eddie, is that of all the database backup being copied, this is the only database experiencing issues, and the UNC location is identical for another database on the same instance. If an Anti-virus tool is interfering, why would it choose just this ONE database's files?
    Jesus Christ: Lunatic, liar or Lord?
    Decide wisely...
  • PDinCAPDinCA Posts: 642 Silver 1
    petey wrote:
    The next time this happens, could you please run

    EXEC master..sqbutility 9997

    to generate a stack trace file, and send me the file ([email protected])? This file is named SQBCoreService_<instance name>_bugreport.txt, and is located in ... 'C:\ProgramData\Red Gate\SQL Backup\Log\' on Windows Vista and newer.

    Having found that the latest (2 minutes after execution) had also failed to delete files, I ran the command on my Windows 8.1 laptop. Having searched for the file on both the SQL Cluster and my Laptop, no such file exists.

    Please advise.
    Jesus Christ: Lunatic, liar or Lord?
    Decide wisely...
  • peteypetey Posts: 2,358 New member
    Are you able to manually delete any one of the files listed as being in use, from the primary server, and also from the file server?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • PDinCAPDinCA Posts: 642 Silver 1
    Only if I restart the SQB Core Service. Restarting appears to release whatever enduring lock the Service retains upon alleged completion of the file transfer.

    I only dealt with the remote files as they're the only ones cited in any "Completed with warnings" log messages.
    Jesus Christ: Lunatic, liar or Lord?
    Decide wisely...
  • PDinCAPDinCA Posts: 642 Silver 1
    Sent another email to ZenDesk in hopes of a response after a three week silence.

    Update, please?
    Jesus Christ: Lunatic, liar or Lord?
    Decide wisely...
  • peteypetey Posts: 2,358 New member
    You mentioned that the backup files of another database copied to the same instance does not experience the same problem. Could you please post the backup command used to back up that database? Just want to compare the options used.

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • PDinCAPDinCA Posts: 642 Silver 1
    Hi Peter,

    Bad DB:
    EXECUTE master..sqlbackup '-SQL "BACKUP LOG &#91;CustomerData&#93; TO DISK = ''E:\DB_Backups\&lt;database&gt;\&lt;AUTO&gt;.sqb'' WITH ERASEFILES = 2, ERASEFILES_REMOTE = 15, FILEOPTIONS = 4, CHECKSUM, DISKRETRYINTERVAL = 30, DISKRETRYCOUNT = 10, COMPRESSION = 4, COPYTO = ''\\bk2waspk\sq2waspk_logs'', INIT, THREADCOUNT = 6, VERIFY"'
    

    Successful DB on same instance:
    EXECUTE master..sqlbackup '-SQL "BACKUP LOG &#91;DataBaseSecurity&#93; TO DISK = ''E:\DB_Backups\&lt;database&gt;\&lt;AUTO&gt;.sqb'' WITH ERASEFILES = 15, ERASEFILES_REMOTE = 15, FILEOPTIONS = 4, CHECKSUM, DISKRETRYINTERVAL = 30, DISKRETRYCOUNT = 10, COMPRESSION = 4, COPYTO = ''\\bk2waspk\sq2waspk_logs'', INIT, THREADCOUNT = 6, VERIFY"'
    

    CustomerData (~30MB files, 3 seconds) runs every 15 minutes; DataBaseSecurity (>1MB, > 1 second) every hour.
    Jesus Christ: Lunatic, liar or Lord?
    Decide wisely...
  • peteypetey Posts: 2,358 New member
    Thanks for posting the details. Noticed nothing significantly different between the 2 set of commands.

    We need to get a stack trace of SQL Backup's activities the next time you encounter this error. If you ran

    EXEC master..sqbutility 9997

    and could not find the SQBCoreService_<instance name>_bugreport.txt file, it may be because the SQL Backup Agent service startup account does not have rights to create the file in that folder. Try granting read/write permissions to the above-mentioned folder to the SQL Backup Agent service startup account.

    On Windows Vista and newer, the file is generated in the 'C:\ProgramData\Red Gate\SQL Backup\Log\' folder on the SQL Server server.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • PDinCAPDinCA Posts: 642 Silver 1
    File posted via email to the 10148 incident.

    Thanks, Peter.
    Jesus Christ: Lunatic, liar or Lord?
    Decide wisely...
Sign In or Register to comment.