4

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)

3
  • AFAIK SQL Server runs CLR stored procedures not in processes but in AppDomains. So I think you will have to restart the server. Just a guess, I'm not an expert of this topic.
    – kol
    Nov 18, 2011 at 11:43
  • Can I ask why you're using a CLR stored proc to export the table rather than SSIS?
    – Ann L.
    Nov 18, 2011 at 21:05
  • Can you show us how you're opening the file for writing and closing it?
    – Kev
    Nov 22, 2011 at 2:17

1 Answer 1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.