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!