Today, we got a call from one of students, complaining that the site doesnt work, so i tried to load the page myself, nope, it didnt work. I connected to the server using remote desktop connection, and everything was working fine. At first i really didnt understand the problem, so i opened the event viewer to see the event logs. The first thing i saw is a warning message saying "Connection pool is full, and can not connect to pool..." , as someone who is using google more than anything else during a day, i opened my browser, and typed the warning into the google. The sites i have found was talking about if you open a connection to the database, and before you close the connection an exception is thrown and u dont use try catch, u have a wasted connection now. And you have too many wasted connection, the pool will be full and you cant make another connection. so watch this :
private bool DoSomethingWithMyDatabase()
{
SqlConnection conn = new SqlConnection...
....
.....
conn.Dispose();
}
if you have a code like above, and an exception is thrown between new SqlConnection and conn.Dispose, you dispose function is never called, and you have one wasted connection now, and if this happens too much in a short time, your pool is going to be full, and your application will stop working. Ok now how do fix it ?
The first dirty patch, is opening up iis manager, and restarting the web application pool and waiting for the next connection full error.
The real patch is, changing ur code, and putting try catch blocks or using block such as:
private bool DoSomethingWithMyDatabase()
{
SqlConnection conn = new SqlConnection...
try{
....
.....
}
finally{
conn.Dispose();
}
}
so even if an exception is thrown, use can safely dispose ur connection, and ur connection will be returned back to the pool. Another solution is :
private bool DoSomethingWithMyDatabase()
{
using(SqlConnection conn = new SqlConnection())
{
}
}