Trace unclosed connections

Question

I get timeout errors every couple of days or so between IIS7 and an SQL 2008 Server. The error message I get is:

System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

Sometimes this sorts itself out, other times I need to manually restart SQL Server. Is there any way I can trace down the cause of this error. I'm 90% sure it's somewhere without our ASP.NET webforms app where a connection is being opened and not closed, but I'm at a loss to pin down the cause.

Answer 1

You can query sys.dm_exec_sessions on the last_request_end_time column to find open yet "sleeping" connections.

From this, you can find out the last SQL executed

SELECT session_id, TEXT
FROM sys.dm_exec_connections c
CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) AS ST 
WHERE c.session_id = <suspicious one from sys.dm_exec_sessions>

Answer 2

I'm 90% sure it's somewhere without our ASP.NET webforms app where a connection is being opened and not closed

I hope in your webforms, you are following the below mentioned approach to dispose the IDisposable objects....

using (System.Data.SqlClient.SqlConnection con = new SqlConnection("YourConnection string")) { 
    con.Open(); 
    SqlCommand cmd = new SqlCommand(); 
    string expression = "Parameter value"; 
    cmd.CommandType = CommandType.StoredProcedure; 
    cmd.CommandText = "Your Stored Procedure"; 
    cmd.Parameters.Add("Your Parameter Name", 
                SqlDbType.VarChar).Value = expression;    
    cmd.Connection = con; 
    using (IDataReader dr = cmd.ExecuteReader()) 
    { 
        if (dr.Read()) 
        { 
        } 
    } 
}