Kill CLR stored procedure process

Question

I have a CLR stored procedure which exports a large table into a text file.

The procedure works fine but. When I manually stop its execution from SQL Management Studio I cannot delete the file it created ().

Error Deleting File or Folder Cannot delete XXXXXXXXXXX: It is being used by another person or program. Close any programs that might be using the file and try again.

I looked for a SQL process (with sp_who2 active) and for a Windows process (with tasklist) on the SQL Machine but I can't identify the process that keeps the file locked.

Please tell me how to kill the process (without restarting SQL server)

Answer 1

You can use handle.exe from SysInternals to see which process has a handle open to your file. But I wouldn't be too surprised if it indeed is the SQL Server process itself (as others have noted in the comments).

First make sure, that your procedure actually closes the file correctly, when being canceled.

You didn't show the code for the procedure, but it is not too far fetched to assume, that you don't close your file (stream) properly in the face of an exception - which is what your procedure will get from SQL Server when being interrupted by a user (more specifically a ThreadAbortException).

Note though, that even if you do handle this correctly, there are still possibilities (e.g. error handle takes "too long") for this cleanup code to fail or not execute to completion, hence leaving the file open.

You may want to check this blog entry for further information.