connection leak issue in SQL Server

Question

Hello everyone,

I am using SQL Server 2008 Enterprise + C# + ADO.Net + .Net 3.5. I am using connection pool at ADO.Net client side (the default behavior of ADO.Net). I am using sp_who2 or sys.dm_exec_connections to find active connections numbers (let me know if my method to find active connection numbers are wrong).

If each time after I create and open a new ADO.Net connection object, I will have a matched close statement to close the ADO.Net connection object instance after using the connection object (to execute store procedure), I am wondering in this scenario, the active connection number should always be 0 after I close all connection from my ADO.Net client application, and if show active connection number > 0, it should be very weird?

thanks in advance, George

Answer 1

Close will free the connection to be available for the connection pool. It doesn't mean the physical connection is closed.

Also make sure the .close is in a finally, so it executes both on the regular flow and when an exception occurs.

Answer 2

Make sure that you properly close or dispose the SqlConnection. Disposing a 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 a .Close() before the end of the using.

You can also clear all pending connection that have the status Awaiting Command by calling

SqlConnection.ClearAllPools();

Answer 3

Not so, the ADO.NET connection pool will return your connection to the pool after Close is called. The connection may not really be closed at this point. To force the connection to be closed try clearing the pool before closing. Try this and you should see the connection close from the server.

var conn = new SqlConnection("a server goes here");
        conn.Open();

        SqlConnection.ClearAllPools();
        conn.Close();