SQL Backup delete issue (error 32)

lculley

We are intermittantly getting the following error for our log backups:

Warning 161: ERASEFILES error: Failed to delete file: \\servername\fileshare\dbname\LOG_(local)_dbname_20101230_030007.sqb. Error code: 32 (The process cannot access the file because it is being used by another process).

The file to be deleted is 8 days before the current date.

We have the following settings on the backup command:



WITH ERASEFILES = 8, MAILTO_ONERROR = email@company.com'', DISKRETRYINTERVAL = 30, DISKRETRYCOUNT = 10, COMPRESSION = 4, THREADCOUNT = 15, VERIFY"', @exitcode OUT, @sqlerrorcode OUT

We do not have anti virus running on the backup server that we are deleting from and the file ends up being deleted by the time we look into the issue.

How does the delete work? Is it using the windows API or a file delete command? We have over 250 files in the directory where it had an issue but each file is only 100 kb to 6 mb.

Could the verify cause an issue of either an old file being used by a process or could it cause windows to get too busy to respond to the delete request?

Thanks in advance!

petey

How does the delete work? Is it using the windows API or a file delete command

Windows API.

Could the verify cause an issue of either an old file being used by a process or could it cause windows to get too busy to respond to the delete request?

Not likely. The verification is only performed on the current backup file. The deletion is failing on an old backup file. The processes are ran sequentially, not in parallel, so verification is completed first before deletion is performed.

You can try to find out which process is using the file. You will need to download this utility, Handle, from the Windows Sysinternals site (http://technet.microsoft.com/en-us/sysi ... 96655.aspx). Place the executable file (handle.exe) in the same folder where the SQL Backup Agent file (SQBCoreService.exe) is installed.

The next time SQL Backup encounters warning 161 and OS error code 32, it will use the Handle utility to report which process is locking the file. You can check the SQL Backup log for the output.

The default folder where the logs are stored is C:\Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Log\<instance name> on Windows 2003 and older, and C:\ProgramData\Red Gate\SQL Backup\Log\<instance name> on Windows Vista and newer.

Thanks.

lculley

Will the handle.exe program execute automatically and log the results to the sql backup log? These errors are occuring at 3 am when no one is available to manually monitor the systems.

petey

Will the handle.exe program execute automatically and log the results to the sql backup log?

Yes it will.

lculley

Sorry, one more question. Our back job backs up to a share on another backup server. If handle.exe is running on the database server will it be able to tell what is holding the file on the backup server?

petey

No it can't. Sorry, didn't notice that the deletion was for a file on a remote share.

lculley

Any other options?

petey

Can't think of any other way to determine what's locking the remote file.

One possibility for the 'file in use' issue is that another SQL Backup process is also reading the files on that directory, hence preventing the file from being deleted. Examples include a SQL Backup restore process iterating through the transaction log backup files to determine which files to restore, or a SQL Backup process reading the files to determine if they should be deleted.

akostin

I have exact same issue when trying to delete old file: Warning 161: ERASEFILES error: Failed to delete file:... Error code: 32 (The process cannot access the file because it is being used by another process).

Basically, I have SQl agent jobs which runs every 10 min and doing backup transaction log backup using SQL Backup 6.5.1.9.
Periodically, few times a day these jobs literally stuck without reporting fail,
backup was created successfully but I can see that job is still running and stuck on ERASEFILES with Error code: 32 (The process cannot access the file because it is being used by another process).

Jobs hangs forever until manually stopped or killed, But even after stopping stuck job file to be deleted is still used by Redgate SQLBackupAgent. Looks like multiple Redgate processes trying to get to the file at same time. Only workaround I found so far is to restart Redgate SQLBackupAgent service, but issue raised again in less than a day.

Any ideas? thanks a lot

petey

Are you backing up the transaction log for multiple databases to the same folder?

Thanks.

akostin

I am running multiple backup jobs for different databases, but in different folders. Each db has it's own folder for transaction log backups. thanks

petey

In that case, only the job backing up the transaction log will ever access the files, unless you have jobs performing full database backups also storing the files in the same directory and also using the ERASEFILES option. Is this the case?

Thanks.

akostin

Not the case. Full and log backups are taken in different folders. I have submitted support ticket #F0054297