I got an error today in my ASP.NET application:
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.
So I found out it has to do with the number of connections open (and not closed). I reviewed all the code, and when Im opening a database as follows:
Dim conString = ConfigurationManager.ConnectionStrings("MyDataBase").ConnectionString
Dim con As New SqlConnection(conString)
Dim SqlString as string = "select username from users"
Dim cmd As New SqlCommand(SqlString, con)
Dim RS As SqlDataReader = cmd.ExecuteReader
Im closing the connection and the datareader as follows too:
I browsed all 469 files on my project to check Im closing the connections, but when I run the following script on SQL:
WHERE DB_NAME(DBID) = 'MYTABLE' AND DBID != 0
I still see a lot of open connections with the value AWAITING COMMAND on the CMD field.
In my connection string, I increased the value of "Max Pool Size" to 500 and the error went away, but I am affraid the error will return.
I also read I can set pooling = false on the connection string, but that would bring performance issues.
So my questions are basically two:
1) Why do I still see the "AWAITING CONNECTION" records? (I see between 50 and 400 constantly). Does this mean I am still having some connections Im not closing?
2) Will pooling = false be a bad move?
3) Suggestions? :)