Timeout expired. Max pool size reached.

[Jake K]

I have a system timer that elapses every 10 seconds and must execute every
ten seconds. Basically every 10 seconds I need to insert into a table. The
following code, however, causes a "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." error at cn.open() after about 2 minutes of executing.

private void LogToTable(string var1)
{
SqlConnection cn;
cn = new SqlConnection(cnString);
SqlCommand cmd;

try
{
cn.Open();
cmd = new SqlCommand("insert into table_name(col1) values('"
+ var1 + "')", cn);
cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message.ToString()));
}
finally
{
if (cn.State == ConnectionState.Open)
cn.Close();
}
}


Any ideas why the connection is not immediately closing in the finally
block? sp_who reports many, many, many connections that never get returned
(or so it appears). This app must run every day all day, every x numberof
seconds.

"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."
at cn.open

 

[Karthik D V]

I have a system timer that elapses every 10 seconds and must execute every
ten seconds. Basically every 10 seconds I need to insert into a table. The
following code, however, causes a "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." error at cn.open() after about 2 minutes of executing.

private void LogToTable(string var1)
{
SqlConnection cn;
cn = new SqlConnection(cnString);
SqlCommand cmd;

try
{
cn.Open();
cmd = new SqlCommand("insert into table_name(col1) values('"
+ var1 + "')", cn);
cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message.ToString()));
}
finally
{
if (cn.State == ConnectionState.Open)
cn.Close();
}
}


Any ideas why the connection is not immediately closing in the finally
block? sp_who reports many, many, many connections that never get returned
(or so it appears). This app must run every day all day, every x numberof
seconds.

"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."
at cn.open

Hi Dude,

I had the same issue sometime back. For me this happened because
it was taking more than a mionute(which is default time out) so figured
out the issue and set the CommandTimeout to some max value.

Set both connection Timeout and Command Timeout to some max value. You
may get a solution.