sql connection

  • Thread starter Thread starter Ertan Sekmen
  • Start date Start date
E

Ertan Sekmen

Hi there,
I required to get over a problem with ASP.net project. I do not have source
code. The error web site raises is : "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"

As I cannot see the source code I am not able to determine what type of
structer the system has. I looked at sql server's process info. It seems the
web site opens connections and do not close them. When the error raises
there are a lot of process with the same user and same database and with
sleeping status.

First thing I try to do is increasing pool size, but then I realized that
this is not the correct solution, because If those connections won't close,
They won't return back to the pool. I am looking for a kind of connection
that may able to closed automatically.

The connection string can be read from web.conf file as follows :

"user id=myuser;password=mypass;initial catalog=mydb;data
source=myserver;max pool size=200;Pooling=true;"

can somebody tell me if there is any connection type closed automatically? I
remember there are things in ADO while I am using VisualBasic6 which I
require neither open nor close the connection.

thanks in advance
Ertan Sekmen
 
Hi Ertan,

IMO the same class that opens a connection should also *Close* it.
Otherwise the connection remains open untill the GC kicks in (it the
connection class is scheduled for gc) or even worse (if it is not scheduled
for GC), it reamins open untill the app shuts down.
So, IMO, the only way would be to correct the faulty program.
Or, as a bad workaround you might try forcing garbage collection by invoking
GC.Collect();
 
Otherwise the connection remains open untill the GC kicks in (it the
connection class is scheduled for gc) or even worse (if it is not scheduled
for GC), it reamins open untill the app shuts down.
Click to expand...

According to the documentation thats not the case:
"Connections that are not explicitly closed might not be added or returned
to the pool. For example, a connection that has gone out of scope but that
has not been explicitly closed will only be returned to the connection pool
if the maximum pool size has been reached and the connection is still
valid."

I have no idea if it actually works though, Ive not used any connection
pooling yet.
automatically?
Well, no.. But if you decrease the timeout on a connection on the SQL server
to something low (1-5 mins) that *should* fix the problem according to this
quote from the documentation:

"The connection pooler will remove a connection from the pool if the
connection lifetime has expired, or if the pooler detects that the
connection with the server has been severed."

But as the connection pool should have released connections if they have
fallen out of scope once the maximum limit is reached it sounds like there
is a memory leak problem (the connections arent released for some reason
even though they arent being used) in the code that you dont have the source
code for.

So if you can get the source code, or simply just rewrite the module you
dont have the source code for, then that would be the most stabile solution
to your problem..

Hope that helps
/Dan
 
Back
Top