IIS Connection Pool

The last few days we see this error message in our website too much:

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

We have not changed anything in our code in a while. I revised the code to check open connections which didn't close, but found everything to be fine.

  • How can I solve this?
  • Do I need to edit this pool?
  • How can I edit this pool's max number of connections?
  • What is the recommended value for a high traffic website?

Update:

Do I need to edit something in IIS?

Update:

I found that the number of active connections are anywhere from 15 to 31, and I found that the max allowed number of connections configured in SQL server is more than 3200 connections, is 31 too many or should I edit something in the ASP.NET configration?

Replay

In most cases connection pooling problems are related to "connection leaks." Your application probably doesn't close its database connections correctly and consistently. When you leave connections open, they remain blocked until the .NET garbage collector closes them for you by calling their Finalize() method.

You want to make sure that you are really closing the connection. For example the following code will cause a connection leak, if the code between .Open and Close throws an exception:

var connection = new SqlConnection(connectionString);
connection.Open();
// some code
connection.Close();

The correct way would be this:

var connection = new SqlConnection(ConnectionString);
try
{
     connection.Open();
     someCall (connection);
}
finally
{
     connection.Close();
}

or

using (SqlConnection connection = new SqlConnection(connectionString))
{
     connection.Open();
     someCall(connection);
}

When your function returns a connection from a class method make sure you cache it locally and call its Close method. You'll leak a connection using this code for example:

var command = new OleDbCommand(someUpdateQuery, getConnection());
result = command.ExecuteNonQuery();
connection().Close();

The connection returned from the first call to getConnection() is not being closed. Instead of closing your connection, this line creates a new one and tries to close it.

If you use SqlDataReader or a OleDbDataReader, close them. Even though closing the connection itself seems to do the trick, put in the extra effort to close your data reader objects explicitly when you use them.



This article "Why Does a Connection Pool Overflow?" from MSDN/SQL Magazine explains a lot of details and suggests some debugging strategies:

  • Run sp_who or sp_who2. These system stored procedures return information from the sysprocesses system table that shows the status of and information about all working processes. Generally, you'll see one server process ID (SPID) per connection. If you named your connection by using the Application Name argument in the connection string, your working connections will be easy to find.
  • Use SQL Server Profiler with the SQLProfiler TSQL_Replay template to trace open connections. If you're familiar with Profiler, this method is easier than polling by using sp_who.
  • Use the Performance Monitor to monitor the pools and connections. I discuss this method in a moment.
  • Monitor performance counters in code. You can monitor the health of your connection pool and the number of established connections by using routines to extract the counters or by using the new .NET PerformanceCounter controls.

Did you check for DataReaders that are not closed and response.redirects before closing the connection or a datareader. Connections stay open when you dont close them before a redirect.

We encounter this problem from time to time on our web site as well. The culprit in our case, is our stats/indexes getting out of date. This causes a previously fast running query to (eventually) become slow and time out.

Try updating statistics and/or rebuilding the indexes on the tables affected by the query and see if that helps.

Unless your usage went up a lot, it seems unlikely that there is just a backlog of work. IMO, the most likely option is that something is using connections and not releasing them promptly. Are you sure you are using using in all cases? Or (through whatever mechanism) releasing the connections?

You can specify minimum and maximum pool size by specifying MinPoolSize=xyz and/or MaxPoolSize=xyz in the connection string. This cause of the problem could be a different thing however.

I have encountered this problem too, when using some 3rd party data layer in one of my .NET applications. The problem was that the layer did not close the connections properly.

We threw out the layer and created one ourselves, which always closes and disposes the connections. Since then we don't get the error anymore.

Use this:

finally
{
    con.Close();
    con.Dispose();
    SqlConnection.ClearPool(con);
}

If you are working on complex legacy code where a simple using(..) {..} isn't possible - as I was - you may want to check out the code snippet I posted in this SO question for a way to determine the call stack of the connection creation when a connection is potentially leaked (not closed after a set timeout). This makes it fairly easy to spot the cause of the leaks.

This is mainly due to the connection not been closed in the application. Use "MinPoolSize" and "MaxPoolSize" in the connection string.

In my case, I was not closing the DataReader object.

        using (SqlCommand dbCmd = new SqlCommand("*StoredProcedureName*"))
        using (dbCmd.Connection = new SqlConnection(WebConfigurationAccess.ConnectionString))
            {
            dbCmd.CommandType = CommandType.StoredProcedure;

            //Add parametres
            dbCmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int)).Value = ID;
.....
.....
            dbCmd.Connection.Open();
            var dr = dbCmd.ExecuteReader(); //created a Data reader here
            dr.Close();    //gotta close the data reader
            //dbCmd.Connection.Close(); //don't need this as 'using' statement should take care of this in its implicit dispose method.
            }

Category: .net Time: 2009-03-22 Views: 11

Related post

  • How does connection pooling work on IIS? 2011-05-12

    I am posting this on behalf of a friend. Is there a document that explains how does IIS connection pooling work, especially in clustered environments (with load balancing, and databases in the backend)? I know the question may seem a little vague. If

  • How can I monitor the connection pool in IIS? 2009-11-18

    On a Win2003 server, a site running Classic ASP (not asp.net) is having performance problems. How can I monitor the performance of the connection pool? The backend SQL Server database (on another server) is nowhere near breaking a sweat, and the serv

  • Enabling Classic ASP Connection Pooling in IIS 6.0 2010-06-25

    We have an application running on Classic ASP which I'd like to get to use connection pooling. The application currently uses this as its connection string: "Provider=MSDASQL; Driver={SQL Server}; Server=db.example.com; Database=DBName; UID=Username;

  • Build server: Recycle IIS application pool on remote server without a VPN connection 2011-03-02

    I have a build server (Windows Server 2008) set up to pull down code from our SVN repository and build the web applications we develop. My current process involves using WinSCP to perform a remote sync that sends the updated files to our production s

  • MAX Connection Pool Setting SQL Server 2008 2012-03-29

    We are expecting a large number of users to hit a Website built with IIS/.Net 4.0 that our SQL Server 2008 database server is providing data for. The database is around 2GB in size. We are contemplating increasing the MAX CONNECTION POOL to between 5

  • IIS Application Pool cannot find path? 2014-09-16

    I am trying to deploy SharePpoint 2010 web parts (through Debug in VS 2010) and I keep getting this error: "Error occurred in deployment step 'Recycle IIS Application Pool': Win32: The system cannot find the path specified." The port number of t

  • Error occurred in deployment step 'Recycle IIS Application Pool' 2015-10-13

    I am trying to build a webpart project and I am trying to deploy the same into my Sharepoint dev site which I created using the Sharepoint Central Admin. But the issue which I am facing while deploying the website from Visual Studio is in the step 'R

  • JDBC read-only connection pools on Glassfish 2009-07-21

    Is it possible to create a read-only connection pool in Glassfish, talking to MySql, so that no updates are allowed? I want to have people use a replicated MySql database and I want to make sure no one tries to do updates. --------------Solutions----

  • How do I determine if connection pooling is working? 2009-08-20

    Is there a reasonably simple way to measure (at the OS level) that connection pooling is working? I'm expecting to see a number of connections being established and staying that way. I suppose I'm only interested in tcp connection establishment/closi

  • Timeout Expired: Connection Pool 2009-09-23

    Here is the error we are getting. We moved app and db servers to x64 from 32-bit. Framework 2.0 service pack 2 is installed on the servers. Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occur

  • Tomcat's connection pool opens only 20 connections to mysql 2009-12-22

    I have a java application with following line in context.xml <Resource driverClassName="com.mysql.jdbc.Driver" maxActive="100" maxIdle="30" initialSize="10" maxWait="7000" name="jdbc/app" pass

  • Restart IIS App Pool Automatically 2010-02-02

    We have a web application that is not completely bulletproof, and on occasion the application pool will die off and not restart without user input. Once it is restarted, it will run just fine for days or even months. Is there a way to have it restart

  • Database Connection Pooling in Servlet - Best Practice 2010-02-22

    I have a Java-JSF Web Application on GlassFish, in which I want to use connection pooling. Therefore I created an application scoped bean that serves with Connection instances for other beans: public class DatabaseBean { private DataSource myDataSour

  • Connection pooling through an F5 Big-Ip SQL VIP 2010-07-06

    I've got a Big-IP pool managing a couple SQL servers in an active/inactive rollover approach - there's only ever one in the pool at a time. Initially I was enabling/disabling the nodes in the pool, but I found that due to connection pooling, active c

  • Differences in how connection pooling is handled in Windows Server 2003 and Windows Server 2008 r2? 2010-09-21

    We just ran into and issue where our connection pool was being flooded by SqlDataReaders but when we ran our load testing on our staging environment we could not recreate the issue. The only thing I can think of is that there are differences in how c

  • Does Apache with PHP use same connection pool for streaming files? 2010-12-01

    Say that I have a PHP site running on that hosts larger images. Each page is very simple and takes virtually no time to process in PHP, but it might contain couple MB large image. The question is, if I have Apache set up to handle 100 connections, an

  • IIS App Pool Identity Internet Settings 2011-01-17

    How does an IIS App Pool determine its Internet Settings? I'm specifying a custom identity under which to host a .NET web application, a service account that is part of our Active Directory domain. When the application runs, it needs to make HTTP req

  • How to Troubleshoot Timeout on Web Requests, Connection Pool Errors, Data Locking, etc 2011-01-18

    I have a question, and am hoping someone here can help. I am running a .NET web application, and am receiving an error under certain circumstances. The error is "System.Data.SqlClient.SqlException: Timeout expired." Though this is being generate

  • jboss 5.1 mysql connection pooling 2011-02-14

    I am using JBOSS 5.1.0.GA, MySQL 5.5 and Hibernate 3.3.1 GA (included with JBOSS) + Spring. My question is do I need to add c3p0 as a data source in my spring/hibernate configuration for connection pooling or are the setting in the JBOSS mysql-ds.xml

iOS development

Android development

Python development

JAVA development

Development language

PHP development

Ruby development

search

Front-end development

Database

development tools

Open Platform

Javascript development

.NET development

cloud computing

server

Copyright (C) avrocks.com, All Rights Reserved.

processed in 1.029 (s). 13 q(s)