Recently one of our Client went through a situation in which databases were becoming unavailable. We investigated the problem and searched for possible solutions.
Here is the summary of the activity
Findings
The error found in the client's database server log is
“FCB::Open: Operating system error 32(The process cannot access the file because it is being used by another process.)”
Other messages do not seem to be suspicious
There are three popular causes of this error
Abnormal Shutdowns:
In case system abnormally shuts down due to power failure or was forced to shutdown due to a “Not responding state”, operating system processes like “Write caching on device” are not completed properly and some files are not released.
Due to this some databases are shown as suspect on restart
Solution:
Uncheck the “Enable write caching on device”, which can be found in settings of partition/drive. The settings can be opened via the Hardware Tab which gets displayed when properties are of the drive are accessed
MDF Files Used by other Processes:
There may be antivirus or backup routines scheduled on the system. SQL Server starts a particular database when it is accessed by users. In case when MDF or LDF file is already in use by an anti-virus scan or backup schedule, then the operating system does not allow SQL Server to use the particular database file
Solution:
Exempt Database files from anti-virus scans or schedule scans and backups at a time when Database is not in use
Note: Process Explorer is an off-the-shelf tool that can be used to investigate which processes are actually using/locking particular file.
Limited privileges to Logon Account:
Some technical resources suggest that the problem may be due to limited privileges to log-on account
Solution
Grant privileges to the Logon account so that it is able to access LOG folder of MS SQL Server
