I have a C# application that communicates with SQL Server.

When I try to drop a user that recently had a connection with SQL Server, it gives me an error that I can't delete them because they have connections open in the database.

All SQL queries are appropriately wrapped in using statements but when I run sp_who, it tells me that that person has a connection in a sleeping state, I have researched this and found out that this is normal behavior meant for speed improvements.

To get rid of this connection, I used SqlConnection.ClearPool which works and I can now delete the user but then the queries becomes a decent amount slower, which I expected and know the reasons why it's happening.

What's the best way to deal with this? Force the connections closed only when trying to delete the person somehow? If so, how is this done?

Thanks in advance!

  • 5
    Why you want to drop the session if it is inactive or sleeping, unless until its causing any sort of blocking in other process? Do you see any performance related issues? – KASQLDBA Apr 23 '15 at 13:40
  • If you are concerned about this, consider revising your client application to disconnect after some period of time. – RLF Apr 23 '15 at 13:50
  • I've considered this... is this the best alternative? But this really doesn't solve the issue - it just reduces the odds that the issue will occur. Say, for example, the client disconnects after 30 seconds (which is a somewhat realistic max connection time). During those 30 seconds, the User cannot be deleted. – Panh Apr 23 '15 at 14:01

When I try to drop a person that recently had a connection with SQL Server

Why are you doing this? What drove the need to have to do this?

It sounds like you're running into connection pooling. But connection pooling is there to help you, it's a great optimization. By clearing the pool you're wiping out this caching mechanism.

My recommendation, without knowing more about what you are trying to do or what you need, would be to leave it as it is and let connection pooling do what it does best.

| improve this answer | |
  • Thanks for your response! And that's exactly it (I think)! The reason this came up is because, let's say, the user leaves the company, or the username was wrong, or they are just messing around and adding and deleting users. I feel like deleting a user is a very common scenario that should be handled gracefully. But the user can't be deleted because the connection is still open - and it can stay open for a VERY LONG time! So waiting for it to close on it's own seems "wrong." So what's the "right" way to go about this? – Panh Apr 23 '15 at 13:57
  • 1
    @Paul Maybe you are giving the permission to create users and "mess around" a little too freely. Is everyone with access to the server a sysadmin? You probably have more to worry about than you realize. – Aaron Bertrand Apr 23 '15 at 13:58
  • No, only very few people have access to do this. But they still should be able to do it. It might not be a "big deal" - maybe it's very unlikely that this will ever even happen - but I'd still like to know the answer. – Panh Apr 23 '15 at 14:04
  • 2
    @Paul So, if it's only a few, teach them how to do it right. In the meantime, if you have a login you can't drop because they have an active session, that is what KILL is for. – Aaron Bertrand Apr 23 '15 at 14:31
  • What do you mean by "Do it right?" What's the "right" way? (They only have access to the database through sprocs). Your KILL command got me looking into it and it looks like I could maybe alter this to find the user that is to be dropped and drop all of their active connections before deleting them. Thanks, Aaron! – Panh Apr 23 '15 at 14:56

Killing the connection processes did not work for me since the clients do not have permission to see process ID's.

In hopes that this might help someone else, this is what I ended up doing; call into SqlConnection.ClearAllPools immediately prior to trying to delete a User. This will truly disconnect any remaining connections by the open application (i.e. if I logged in under User1, then logged in as User2 and delete User1 all within the same application process). I considered this likely if someone is just giving a demo of the software, etc.

Regarding the less likely scenario where User1 logged in and User2 attempts to delete User1 from a different computer/process, the stored procedure attempts to delete the User from SQL Server first and if this throws an exception, I wrapped the stored procedure in a try-catch so that it does not try to continue deleting the user out of database tables, etc.; thus, keeping the database's integrity. The catch propagates the exception with a Throw and the client application catches this exception and handles it gracefully.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.