All Pooled Connections In Use

[john bailo]

SqlConnection.Open, from a web application, threw:

/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./

What would cause this error?

 

[Hussein Abuthuraya[MSFT]]

John,

A common cause of that error is that all the 100 default connections (Max Pool Size=100) in the pool are in use. Whey they would be all in use? Because you don't call the
Close() method on each connection object that you open and use when finished using it.

100 connections in the pool must be sufficient for most applications, however in specific scenarios you may want to increase that by setting the "Max Pool Size" in the
connection string.

If you want the pool to function well, please consider the following:
- Open the connection as late as possible (by calling the Open method immediately before using it) and don't use global connections.
- Close the connection as early as possible (by calling the Close method immediately when finished using it).

So review your code and make sure that for every Open there is a matching Close call.

I hope this helps!


Thanks,
Hussein Abuthuraya
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure? For information about the Microsoft Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.

 

[Michael Bird]

I'm assuming that since you are asking this wuestion, you don't expect your
code to have a lot of concurrent open connections to your SQL data source.
I would quess that you are not closing your connections correctly when your
are finished with them and are running out of available connections.

 

[john bailo]

I'm assuming that since you are asking this wuestion, you don't expect your
code to have a lot of concurrent open connections to your SQL data source.
I would quess that you are not closing your connections correctly when your
are finished with them and are running out of available connections.

Yes, and no. I guess I had to refine my ideas about what 'closing a
connection' means.
I was closing the mySqlConnection, but I did not dispose the mySqlCommand
however.

Now my squence after consuming a Connection/Command set is:

mySqlCommand.Dispose
mySqlConnection.Close
mySqlConnection.Dispose

 

[Angel Saenz-Badillos[MS]]

John,
There is only one reason I can think of where adding command dispose would
seem to fix your problem, and since it does not really fix it I need to ask.
Are you closing your connection in a Finalizer (destructor)?

This is one of the most common problems with people starting to use the .net
framework and we have not done a very good job of putting out the message,
you cant use finalizers to clean up managed resources, period.

If you are disposing the connection in a finalizer you will get stress
related errors in production, this problem was so bad that I had to add the
following comment to SqlConnection Close documentation:
http://msdn.microsoft.com/library/d...tasqlclientsqlconnectionclassdisposetopic.asp
CAUTION Do not call Close or Dispose on a Connection, a DataReader, or
any other managed object in the Finalize method of your class. In a
finalizer, you should only release unmanaged resources that your class owns
directly. If your class does not own any unmanaged resources, do not include
a Finalize method in your class definition. For more information, see
Programming for Garbage Collection.

Hope this helped,