Connection timeout

[DCraig]

I'm having a problem with one of my forms (using .Net 03 EE, SQL 2K
Standard, XP client and Windows Server03). After editing and deleting a few
records, the form starts timing out, giving me the message;

"Timeout expired. 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."

Once this happens, I cannot connect to the database until I restart the
application. At the same time, I can connect to the database fine from
another application (query analyzer) or even another copy of the same
application. I understood that the 'connection pool' was a resource on the
server, not part of ADO .Net, but either the message is complete nonsense or
each instance of the application has a limited pool of connections?

I've managed to improve the situation by sharing a connection across the
tiers and passing it by reference, but I still get the message after editing
10 or so records rather than 3. I've been going over the code to be sure
that connections are being closed and transactions are terminating and it
all looks like it should: the connection is being closed in 'finally' as
recommended, previous transactions are terminating successfully.

I've monitored the connections on the server, and typically there are 40 to
60 connections (this includes connections to the live database as well as
our development copy) and 10 to 20 pools. From what I've read this is more
than sufficient, the number does not change significantly when the exception
occurs.

Has anyone seen this? Can anyone help me understand if this has to do with
the connection pool or if the message is a wild goose chase?

Thanks;

David Craig.

 

[William \(Bill\) Vaughn]

Ah, no. The Connection Pool is a mechanism on the CLIENT, not the server. Typically connection timeouts are caused by either:
1.. Not closing connections. For example, if your routine opens a connection, executes a query that throws an exception, the Close might not get called. You might also be handing a DataReader to another routine that does not (or cannot) close the Connection.
2.. Overloading the server. If the application is pressed so hard that it can't get the job done in the time available (before another operation begins), another Connection is created. This can easily snowball and fill the Connection Pool.
You can't really share a live connection across processes. You can share the ConnectionString, but not the Connection class. Each process gets its own pool.

See Chapter 9 and my article on handling the Connection Pool on my website. http://www.betav.com/sql_server_magazine.htm

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
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.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------