ERASEFILES on remote server fails for just 1 DB

PDinCA

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.

petey

The next time this happens, could you please run

EXEC master..sqbutility 9997

to generate a stack trace file, and send me the file (peter.yeoh@red-gate.com)? 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.

Eddie D

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

PDinCA

eddie davis wrote:

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?

PDinCA

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 (peter.yeoh@red-gate.com)? 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.

petey

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?

PDinCA

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.

PDinCA

Sent another email to ZenDesk in hopes of a response after a three week silence.

Update, please?

petey

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.

PDinCA

Hi Peter,

Bad DB:

EXECUTE master..sqlbackup '-SQL "BACKUP LOG [CustomerData] TO DISK = ''E:\DB_Backups\<database>\<AUTO>.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 [DataBaseSecurity] TO DISK = ''E:\DB_Backups\<database>\<AUTO>.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.

petey

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.

PDinCA

File posted via email to the 10148 incident.

Thanks, Peter.