Challenge:
Have you seen following error?
timeout-expired-the-timeout-period-elapsed-prior-to-obtaining-a-connection-from-the-pool-this-may-have-occured-because-all-pooled-connections-were-in-use-and-max-pool-size-was-reached
Then this post is to solve it!
Solution:
As per the error your code has not closed the opened SqlConnection properly. For example
SqlConnection conn = new SqlConnection(
myConnectionString);
conn.Open();
doSomething(); /* If some error occurs here — Next line will not get called and it will leave connection open */
conn.Close();
Solution:
1.
SqlConnection conn = new SqlConnection(myConnectionString);
try
{
conn.Open();
doSomething(conn);
}
finally
{
conn.Close(); // This line will get called in any case — success/failure
}
So, open your solution in Visual Studio and search in entire solution for all open connections and for all the code implement above suggested solution.
conn.Open();
doSomething(); /* If some error occurs here — Next line will not get called and it will leave connection open */
conn.Close();
Solution:
1.
SqlConnection conn = new SqlConnection(myConnectionString);
try
{
conn.Open();
doSomething(conn);
}
finally
{
conn.Close(); // This line will get called in any case — success/failure
}
So, open your solution in Visual Studio and search in entire solution for all open connections and for all the code implement above suggested solution.
Just a note : If you have written Data Access layer code in code behind file then you are in trouble here. You have to do changes at N number of places. If you would have created separate Data Access layer (Technically Class Library) and Method to do DB operation then your task would have been easy enough!
2) You can raise the connection pool size in the connection string. For example, you can add “Max Pool Size=100” to your connection string to increase the pool size to 100.
Implement above solutions. You should not see any issues any more.
Implement above solutions. You should not see any issues any more.
Good to read :
http://blogs.msdn.com/b/tolong/archive/2006/11/21/max-pool-size-was-reached.aspx
Happy DB Access! 🙂
Hi,
I think it’s better to pre check the connection state in the finally block like –
if(connectionState.Open == conn.State)
{
conn.Close();
}
or another efficient way is write whole code in using keyword
using (SqlConnection conn = new SqlConnection)
{
conn.Open();
doSomething();
} // Connection is disposed and closed here, even if an exception is thrown
Very good to read : http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx
Yes — Agree! Using is best!
Very Nice