Files On Network Share Constantly Locking

network-sharewindows-server-2016

I recently replaced our old trusty server with a newer model, and took the opportunity to upgrade to Windows Server 2016.

Ever since the upgrade, we're been having a couple of issues, and I'm starting to wonder if they are both related.

First up is an issue with SQL Server 2014, which I posted about here:
SQL Server 2014 – Recovery Pending, Almost Daily

Essentially, the databases are all set to "Auto Close", and frequently (about once a day), they fail to re-open, giving the error "The process cannot access the file because it is being used by another process".

The database files are located in a folder which is shared across the network, although they are only used by the locally installed SQL Server.

However, we also have one of our Mac users who has reported similar issues opening normal files. If he goes into a network share and opens a file, the first file opens fine, sometimes even a second, however eventually when trying to open subsequent files, he gets an error stating the file is locked and in use. This happens to all files on the share, not just in the folder he was working in. He needs to disconnect and reconnect before he can open any more files.

We've disabled previewing in the mac file browser, however it appears to make no difference. All virus checkers and backups have been suspended to prevent them locking the files, yet still it continues.

Any suggestions?

Best Answer

The first thing I would do is take those SQL database files and lock them down so that only the service account that SQL runs under can open them. That way, even though they are sitting inside a shared folder, nobody on the network can interfere with them. Also, no other applications on the host (except those that run under the SQL account) can interfere with them.

What you can do after the fact is just start digging. Windows has some built-in tools to see if anyone over the network is locking a file via the Shared Folders MMC snapin.

Another set of extremely useful tools is pstools which has command-line tools for viewing remote locks.

Finally Process Explorer is an excellent tool for seeing what's going on. When you have a lock situation, fire up Process Explorer and search for that file handle, and see what's locking it. Investigate from there.

Related Topic