Troubleshooting Timeout expired. All pooled connections were in use and max pool size was reached.

[jobs]

Re: Troubleshooting Timeout expired. All pooled connections were in
use and max pool size was reached.

New webservers. win2003. IIS6. asp.net 2.0/ sql server 2005 and Oracle
9i through a 64 bit Oracle client. Everything working fine until this.

I started getting these errors as the new website activity started to
heat up.

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.

After inspection of some of my methods I did see a few returning data
before disposing and closing.

I noticed these in the event log:

Event message: An unhandled exception has occurred.

The problem went away (at least for now) when I cycled the
applicationpool in IIS for the site.

A few dumb questions.

1. I did not see any significant activity on the webserver. How can I
troubleshoot this? Any way to see how many open connections? sessions
on the Web server? etc.

2. Looking at application pool setting, I thought I'd set down the
idle type before it recycles idle process to 5 minutes from 20
minutes. Good idea? what other settings might improve things?

Eventually I plan to loadbalance this webserver. It's currenty on
failover to an identical webserver.

Thank you for any help or information!

 

[bruce barker]

while a real high volume site (100+ concurrent users) might see and
require the pool sizes to be bumped up, usually its a coding error. the
database connections are not closed and returned to the pool. be sure
all code has a try/finally block that closes the connection or use the
using statement.

be sure connection are local, and never passed around, placed in session
or global variables. the routine that opens the connection, should close
before exiting. failure to close on error is a common defect.

-- bruce (sqlwork.com)