Connection pool error

  • Hi,

    Can anyone get back to me with tips regarding this.

    I am gettign connection pool error while using the .Net application. When i checked the Activity monitor on sql server 2005, i found that many process ID where presenet with status sleeping. When i check the details for these process ID, i found the SQL query in that. I then checked the applciation which executes the query..and found the connection is closing as well as doing a dispose() even on the connectin object. Then why does the activity monitor show so many process ID with sleeping status...??What does sleeping mean..???Any link i can refer to...??How to kill these process all togetther..??Now i restart when ever this prblem occurs..!!! or mnauly kill each process

    Looking forward to your suggestions..thanks...

  • If you have a connection pool set by your provider - web server or ODBC source the connection will be there after Connection.Close statement.

    For example, go to Start->Administrative Tools->Data Sources (ODBC) to open ODBC Administrator. Click on the Connection Pool tab. Scroll down to SQL Server driver and double-click on the words SQL Server. The Set Connection Pooling Attributes opens. If the connection pooling is selected, there is a box to enter time for how long the unused connection stays in the pool.

    Regards,Yelena Varsha

  • What is the specific error you are getting?

  • Thanks for the response...here is the error message..

    "Timeout expired. The time out expired prior to optaining connection frm the pool.All pooled connections were in use and max pool size was reached."

    Looking forward to your suggestions...

  • This is most likely an application issue. I have seen it when an application was leaking connections (wasn't explicitly closing out the connection but calling an open for a new one each time). Here is a blog post from Angel Saenz-Badillos which covers some of the reasons you can receive this error.

    Connection Pooling and the "Timeout expired" exception FAQ

    K. Brian Kelley
    @kbriankelley

  • We're have the same issue.

    We have a problem in our environment. We use a .NET application that uses a 'buggy' stored procedure. The maximum number of connections to the database is set to 99. I think this is part of the code and can not be changed. I have called the support for the application.

    When we reach that limit, everything stops working. Right now, we have to manually keep an eye on the number of open connections to this database. We have to go into the Activity Monitor and keep track of this. I want to set up an alert that will automatically send an email when the number of connections to a particular database goes over 85. How would I do this? I looked into the standard counters etc and nothing seems to work. The connections that cause the issue are the ones initiated by the sotred procedure. For some reason, it fails to release some of the connections. Right now, when we hit this situation, we are simply rebooting the SQL server. That solves the issue.

    I have set up operators and database mail to work. I just need to figure out how to set up the Alert in this case.

    Would it be possible to kill some of the connections as an 'action' or 'response' to this alert? That would be great!

    Any help?

    Thank you.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply