5

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.

migrated from stackoverflow.com Jan 26 '12 at 14:15

This question came from our site for professional and enthusiast programmers.

4

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>
  • Hi @gbn, at what columns do I need to check to find the 'open yet sleeping connections'? Does this mean where last_request_end_time is null and status is sleeping? – Dirk Boer Jun 16 '18 at 21:13
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()) 
        { 
        } 
    } 
}
  • Nice to know about the IDisposable. – StanleyJohns Jan 27 '12 at 18:16

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.