Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)

share|improve this question
    
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 '11 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 '11 at 21:05
    
Can you show us how you're opening the file for writing and closing it? –  Kev Nov 22 '11 at 2:17
add comment

migrated from stackoverflow.com Nov 22 '11 at 2:47

This question came from our site for professional and enthusiast programmers.

1 Answer

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.