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!
Best Answer
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.