Connection leak issue in SQL Server
I am using SQL Server 2008 Enterprise + C # + ADO.Net + .Net 3.5. I am using ADO.Net client side connection pooling (ADO.Net default behavior). I am using sp_who2 or sys.dm_exec_connections to find the active connection numbers (let me know if my method finds the active connection numbers incorrect).
If every time after creating and opening a new ADO.Net connection object I have an associated close statement to close the ADO.Net connection object instance after using the connection object (to execute the store procedure), I wonder in this case the active connection number should always be 0 after I close all connection to my ADO.Net client application, and if you show active connection number> 0, it must be very strange?
thanks in advance george
source to share
Not like that, ADO.NET connection pooling will return your pool connection after calling Close. The connection cannot be closed at this time. To force the connection to close, try flushing the pool before closing. Try this and you will see a connection close to the server.
var conn = new SqlConnection("a server goes here");
conn.Open();
SqlConnection.ClearAllPools();
conn.Close();
source to share
Make sure you close or select correctly SqlConnection
. Removing the connection will automatically close it.
using(SqlConnection connection = new SqlConnection(connectionString))
{
} //The connection will close automatically here
But you can be explicit and still do .Close () until the end of use.
You can also clear all pending connections that have the Pending Status command by calling
SqlConnection.ClearAllPools();
source to share