2

This issue applies for SQL Server 2019 Always on environment with the latest CU applied.

When SQL service needs a restart or the entire OS needs a reboot, some of the DBs come back in recovery pending/ suspect mode. The logs show the reason due to this is because the log files are in use by a spearte process. There are not any specific DBs this reoccurs on, mostly random DBs, however some of the same DBs have appeared in failed state before. Sometimes the errors show in primary, other times the errors are reported on secondary. It is never consistent. The error is reported in the logs like seen below:

FCB: Open Failed: Could not open file 'DB_NAME.ldf' for file number 2. OS error: 32(The process cannot access the file because it is is being used by another process) 

Database 'DB_NAME' startup failed with error 5105 severity 25 state 6. 

I have done the following:

  • clean shutdown of SQL prior to rebooting OS.
  • ran DBCC checkDB prior to restarting SQL , no issues were seen
  • Antivirus exclusions have already been set

Looking in combination of resource monitor & event viewer, I was able to identify a SYSTEM Kernel process is locking the .ldf files - Filter Mananger (PID 4). After doing some research on what Filter Manager is, I have found Filter Manager is part of Windows and allows 3rd party mini drivers to run. I am unsure why and how they would even access SQL .ldf files each time the service requires a planned restart.

To resolve this issue, we just restore from clean full & log backups. However I would like to understand in detail why this is occurring and how to prevent it from occurring during an unplanned or sudden shutdown/restart.

Does anyone have feedback as to why this could be happening. If you have faced this issue before, please provide assistance on how to mitigate this issue.

Let me know if any additional details are needed. TIA!

1 Answer 1

2

FCB: Open Failed: Could not open file 'DB_NAME.ldf' for file number 2. OS error: 32(The process cannot access the file because it is is being used by another process)

Also

Antivirus exclusions have already been set

Pretty much no one understands what "exclusions" mean. It doesn't mean the software won't touch it, it means that the software will still do whatever it wants with it but might exclude certain checks.

Looking in combination of resource monitor & event viewer, I was able to identify a SYSTEM Kernel process is locking the .ldf files - Filter Mananger (PID 4). After doing some research on what Filter Manager is, I have found Filter Manager is part of Windows and allows 3rd party mini drivers to run.

If you do an xperf/wpr for minifilters it's possible to look into it further. My guess is it's the antivirus. I'm biased as it's almost always antivirus in these specific scenarios.

I am unsure why and how they would even access SQL .ldf files each time the service requires a planned restart.

Minifilters (among other items) work on a pass down callback infrastructure. They register for the events they are interested. The current top filter receives the item, does whatever it wants, then calls the callback to filter manager with different options (for example, I did something, or I don't care about this). Why does this matter?

Antivirus/Backup Agents/Etc., all want to know what files are being OPENED, SQL Server upon start will attempt to OPEN the files. SQL Server calls the OS CreateFile API, this eventually turns into an IRP, which eventually makes it to the filter manager (which only work on IRPs). So far, the CreateFile (used to open files, create files, and can also delete files funny enough) API has been called but nothing has been served as it hasn't made its way down the IO stack. Let's say the filter driver for antivirus is interested in this specific major IRP and decides it wants to investigate further. The request is now waiting on the filter driver to return to the filter manager (all drivers in that stack, actually). This is regardless of any "exclusions" or not, filter manager has no idea what an "exclusion" is or does.

To resolve this issue, we just restore from clean full & log backups. However I would like to understand in detail why this is occurring and how to prevent it from occurring during an unplanned or sudden shutdown/restart.

The first step is gathering the data, then investigating it, then following up based on who/what is doing the bad thing. WPR/XPerf can be used to gather the data, WPA is my favorite tool to investigate the data. Knowledge of the OS and programming is going to help you understand what things mean.

I have a olved ystery write up about a closely related (not locking, taking a cluster down) that might be helpful.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.