Drowning in the connection pool

  • Thread starter Thread starter Lucas Graf
  • Start date Start date
L

Lucas Graf

First let me state for the record I have searched and tried all suggestions I have found, and nothing seems to have helped so far.

I have 2 threads created to update a UI w/data from the database. The connection/command/reader object are all declared outside of the try statement, and then closed/disposed in the finally portion. The connections are not left open for any length of time greater than what is needed.

I get "Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool."
when..
1) I am connection to the database remotely via VPN over cable modem
-and-
2) using this connection string (note the integrated security)

public const string sConnect = "persist security info = False;packet size = 8192;data source=MYSERVER;Integrated Security = true;initial catalog=MYDB";

I don't get that error when..
1) I am NOT trying to connect over VPN
-OR-
2) When I use this connection string (NOT using integrated security)

public const string sConnect = "uid=sa;pwd=xxxxx;packet size = 8192;data source=MYSERVER;persist security info = False;initial catalog=MYDB";

This issue pretty much started when I tried to switch over to my final settings of not using the sa account and using Windows security for the users. Am I missing a setting on the server or client? Turning pooling off isn't overly viable since it slows to a dead crawl.
 
Consider that this Timeout exception is caused by the inability to get a free connection from the pool. As I discuss in my book and article(s) on connection pooling this can be caused by several factors:
1) You left a connection open. This is the most common cause. We've seen many people say they had closed all connections, but ultimately found that they had not.
2) You're asking the server to do too much work and it can't get it done before another operation comes in and asks it to do more. In this case, the system has to allocate another pooled connection which eventually leads to an overflowed pool. I expect this is your problem. The fact that SSPI authentication has to take place on each (and every) open and you're running on a cable modem (can be very slow) and this causes it to fail tells me that you have a performance problem.
3) Some other application (even your own) is blocking your application.
.... there are other reasons too so I would look at my article (www.betav.com\articles), attend one of my workshops (VSLive in August), Immersion (in October), or search the group archives.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

First let me state for the record I have searched and tried all suggestions I have found, and nothing seems to have helped so far.

I have 2 threads created to update a UI w/data from the database. The connection/command/reader object are all declared outside of the try statement, and then closed/disposed in the finally portion. The connections are not left open for any length of time greater than what is needed.

I get "Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool."
when..
1) I am connection to the database remotely via VPN over cable modem
-and-
2) using this connection string (note the integrated security)

public const string sConnect = "persist security info = False;packet size = 8192;data source=MYSERVER;Integrated Security = true;initial catalog=MYDB";

I don't get that error when..
1) I am NOT trying to connect over VPN
-OR-
2) When I use this connection string (NOT using integrated security)

public const string sConnect = "uid=sa;pwd=xxxxx;packet size = 8192;data source=MYSERVER;persist security info = False;initial catalog=MYDB";

This issue pretty much started when I tried to switch over to my final settings of not using the sa account and using Windows security for the users. Am I missing a setting on the server or client? Turning pooling off isn't overly viable since it slows to a dead crawl.
 
Back
Top