Hi Matt:
Matt said:
I've developed a VB.Net app using SQL Server 2000; after testing populating
a dataset repeatedly (using GUI not programmatically) with a call to a
stored procedure eventually the fill pauses for a longgg time (always on the
99th call for some reason). I can set the timeout to whatever I like and
still the dataset doesn't get filled and I get a connection pool timeout
error.
Not trying to parse words but what specifically is the exception? If you
are calling a fill statement 99 times successively with no interruption,
then it may make sense to leave the connection open for that small duration
and close it when you are done. I'm not advocating leaving connections open
indiscriminately, but the rule is close the connection when you are done
with it, not necessarily close it every time a statement completes (although
it usually ends up being the case b/c you don't often fire successive
commands ).
So if you have ascenario where you are firing something 99 times, try this
connection.open();
for(int i = 0; i< 100; i++){
//fire your query
}
connection.close
There are times when it's ok to leave a connection open although they are
the exception rather than the rule. This would use only one connection and
you know that your pool has this available to it.
Now, the bigger thing is why is this happening? If you are opening anew
connection for each query, are you positive that each previous connection is
closed? The rule is close it when you are done with it, so you may be
accidentally leaving a bunch of them open and running out of connections (it
really sounds like this is the case). Unless this is multihtreaded, you
onlyneed one connection but if you are using 100 different ones, make sure
you have the same connection string for each and that you are clos8ing them
ASAP so that they'll be returned t0o the pool.
As far as I know there are at most 3 connections live at any one
time - Can anyone suggest a reason why this is happening?
Double check this. The 99 thing really sounds like you are running out of
connections... if it was RAM or something else, you'd think it may happen at
different intervals. The real test is to query another smaller table or any
other table and see if 99 is still the magic number. If so, I'll be you are
leaving the connections open somewhere or creating new ones before the old
ones have had time to close (if htis is multihtreaded for instance, you may
be opening up a ton of connections and before they have time to close you
have 99 open).
Also make sure you have the same connection string so they can be returned
to the pool - by the title of your post, I'm assuming that you left Pooling
on by default right?
Is it multithreaded/asynchronous?
Also, it this SQL SErver or Oracle or a DB that has a profiler? If so, see
what's going on behind the scenes with a trace (this is almost always a good
idea) and see how many connections are open at any one given time.
I'm betting it's running out of connections and if it's not multithreaded,
then it's probably as easy as finding the open ones and closing them, or
just using one for the whole batch.
