• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 711
  • Last Modified:

sql server connection pool

i got this error

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.

i made theconnection time out 6000 insteade of 600 (default ) from  sql server management studio
and still getting the same error
1 Solution
On your SQL connection string, there is an option (Max Pool Size) that you can specify to increase the size of your connection pool. The default is 100 if you don't specify any other value. The default value should be sufficient in most cases. If you determine that you need more, you can specify a higher value in your connection.

I hope the default 100 is enough....

Any how, make sure that you use Connection.close to clear the pool..

Most often the problem is because database connections are being opened but never closed. Eventually they will time out and the problem will go away for a while, but will soon return. If you are opening a connection inside a loop, try moving the open/close connections outside the loop or call .close() on the connection at the end of the loop. Examine your code and make sure that you close all of your database connections as soon as you are done with them, this will return your connection to the pool so it can be used by the next database operation. If you don't explicitly close the connection when you are done, it will stay open and out of the pool until it times out, which may be several minutes.

Source: http://social.msdn.microsoft.com/Forums/en/adodotnetdataproviders/thread/c57c0432-c27b-45ab-81ca-b2df76c911ef
Anthony PerkinsCommented:
>>i made theconnection time out 6000 insteade of 600 (default ) from  sql server management studio<<
The timeout is set at the client and not at the server.
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
I suggest enabling MARS for more effective pooling.. Does the trick sometimes with little effort...Read below...

I would first investigate whether or not you are failing to close and dispose a connection once it has been finished with.

You can run stored procedure sp_who on your server to see how many connections your application has, if during usage the number of open connections is building up, you are quite likely to be failing to close one somewhere.

normally you should utilise the USING command when declaring a connection, that guarantees it will be closed when you leave that routine.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now