Hello experts,
I am seeing sporadic (every other day or every few days) errors like this one for a backup job:
Operating system error 32(The process cannot access the file because it is being used by another process.).
I have read that the usual suspect is antivirus or similar scanning. But the timing of the errors I am getting makes me wonder if this full backup could be conflicting with a transaction log backup.
I am going to try moving the backup job time to test out this hypothesis. But generally speaking, does anyone know if this error can be caused by conflicting backups? I just want to learn whether that is possible.
Thanks for any help.
-- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
No, database and/or log backups would not cause this error.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 9, 2023 at 6:08 pm
Hi Scott, thanks. I'll need to work with our systems team to track down any scanning that might be causing this issue.
Thanks again.
-- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
January 13, 2023 at 8:34 am
You have already done some basic troubleshooting, so tracking down the problem may not be easy.
The code producing the message is core functionality so the message has to be taken as correct. Something else holds a lock on the file object at the time the message is produced.
You are already checking for the most obvious problem, a virus scanner, but the clue to the problem may be in the previous sentence.
If you trace the events that occurred in SQL server when an object is updated you will see multiple tasks that get created in sequence and put into the scheduling queue. These tasks get run as time allows. On a very busy system I have seen the unlock task get delayed and block a subsequently issued SELECT from being processed. I use SQL as an example because we have tools that can trace these events and see the detail of what is happening.
NTFS is a database and does much the same processing as SQL when a file is created, but is a black box to most people. When I script a file create followed by a file open I always put in an approx 250ms delay to allow the async task in NTFS that populates the file cache time to complete. Without this I too often see file not found when the open is run. On older servers with spinning disks this problem did not occurr, but with modern solid state storage it has become common.
So without having any evidence to show this, what may be happening could be a NTFS issue. The backup program may be caught within NTFS housekeeping and hit a situation where the NTFS DB lock release on file create has not been processed before the file open runs. Even though the backup will probably create and open the file in a single API call, it is unable to influence the internal timing of running tasks within NTFS.
Again, I use NTFS as an example because it is very common. The same principle of a timing issue could apply with ReFS and just about any other file system.
Moving forward with troubleshooting this will not be easy. You will have to raise a problem with MS and hope you get an engineer with the skills needed to dig into this issue. If it does turn out to be a timing issue within NTFS then the only fix would be an update to NTFS. As they say in Scotland: good luck with tha'. Ultimately if it is a NTFS problem it is only likely to be fixed if multiple customers report the issue and a common cause can be found.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply