ERASEFILES on remote server fails for just 1 DB

Compresses, encrypts, secures and monitors SQL Server backups.

Moderators: JonathanWatts, Chris Auckland, eddie davis, Colin Millerchip, Brian Harris, james.billings, RBA, petey

ERASEFILES on remote server fails for just 1 DB

Postby PDinCA » Thu Dec 19, 2013 10:14 pm

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:
Code: Select all
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.
PDinCA
 
Posts: 512
Joined: Mon Jul 25, 2005 11:42 pm
Location: Costa Mesa, CA, USA

Postby petey » Sun Dec 22, 2013 1:17 am

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.
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 7
petey
 
Posts: 2228
Joined: Sun Apr 24, 2005 12:34 pm

Postby eddie davis » Mon Dec 23, 2013 2:57 pm

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
Technical Support Engineer
Red Gate Software Ltd
E-mail: support@red-gate.com
eddie davis
 
Posts: 942
Joined: Wed Jun 14, 2006 3:47 pm
Location: Red Gate Software

Re:

Postby PDinCA » Tue Jan 07, 2014 8:28 pm

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
 
Posts: 512
Joined: Mon Jul 25, 2005 11:42 pm
Location: Costa Mesa, CA, USA

Re:

Postby PDinCA » Tue Jan 07, 2014 8:40 pm

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.
PDinCA
 
Posts: 512
Joined: Mon Jul 25, 2005 11:42 pm
Location: Costa Mesa, CA, USA

Postby petey » Wed Jan 08, 2014 1:28 am

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 7
petey
 
Posts: 2228
Joined: Sun Apr 24, 2005 12:34 pm

Postby PDinCA » Wed Jan 08, 2014 3:27 am

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
 
Posts: 512
Joined: Mon Jul 25, 2005 11:42 pm
Location: Costa Mesa, CA, USA

Postby PDinCA » Wed Jan 29, 2014 12:15 am

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

Update, please?
PDinCA
 
Posts: 512
Joined: Mon Jul 25, 2005 11:42 pm
Location: Costa Mesa, CA, USA

Postby petey » Wed Jan 29, 2014 8:29 am

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 7
petey
 
Posts: 2228
Joined: Sun Apr 24, 2005 12:34 pm

Postby PDinCA » Wed Jan 29, 2014 9:06 pm

Hi Peter,

Bad DB:
Code: Select all
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:
Code: Select all
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.
PDinCA
 
Posts: 512
Joined: Mon Jul 25, 2005 11:42 pm
Location: Costa Mesa, CA, USA

Postby petey » Thu Jan 30, 2014 3:16 am

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 7
petey
 
Posts: 2228
Joined: Sun Apr 24, 2005 12:34 pm

Postby PDinCA » Thu Jan 30, 2014 10:19 pm

File posted via email to the 10148 incident.

Thanks, Peter.
PDinCA
 
Posts: 512
Joined: Mon Jul 25, 2005 11:42 pm
Location: Costa Mesa, CA, USA


Return to SQL Backup 7

Who is online

Users browsing this forum: No registered users and 0 guests