I just found out about a nifty tool and thought I would share.
Scenario: The nightly ETL uses files as source data to insert into the data warehouse. The ETL failed because the file was locked and not accessible. The file is on a server in a shared folder. I needed to find out who had the file open, although I had a suspicion it was sql agent, not an actual person. Either way, the file needed to be closed, or the connection terminated.
Answer: openfiles.exe
I had never heard of this utility, but it works wonders. It was able to tell me who had the file open and allow me to disconnect the user's session with that file. In my case, the ETL was able to run successfully again. :) I did find out afterwards that our IT department performed maintenance over the weekend which must have caused the connection to 2 of these files to be "hung up".
The link above shows all the options available, but here is a summary also.
You can access the following information on the local server/workstation, or from remote. If you are remotely querying/disconnecting, then use the following switch:
- /s ComputerName - you can use the name or IP of the remote computer.
You can perform all of these functions as yourself, or specify a specific user. You can use the following switches to indicate a specific user:
- /u - specify the username to run the task as, format is domain\user
- /p - enter the password for the username entered
List of files open and by whom
/query
- /fo {TABLE | LIST | CSV} - this alows you to specify the format of the results
- /nh - this allows you to hide the headers in the results, but this switch can only be used when the /fo tag indicates a TABLE or CSV formatted resultset
- /v - this stands for verbose and shows the verbose task information
Terminate the connection
/disconnect
- /id ### - disconnects the file specified; use /query to determine the id of each file
- /a username - disconnects all files opened by the specific username
- /o mode - disconnects all open files that are in the specified mode (values expect either Read/Write or Read)
- /se sessionname - disconnects all open files in the specified session
- /op filename - disconnects the specific file by file name
Note: I tried to disconnect the files using the ID specified from the /query switch. However, it didnt disconnect as I expected. When I tried the /o Read/Write, it did work as expected and closed all files. Also, when looking at files on a software share, you can use the ip address.
Hope this helps someone - I am sure I will be referring back to my own blog for this one a few times!