Connection Pooling and the “Timeout expired” exception FAQ


System.InvalidOperationException: 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.

 

 

Well, it has happened again, Yet Another Thread On Leaked Connections (YATOLC). This is one of the most common and painful pooling problems out there, so I guess it is time to post a complete FAQ type answer. The reason this is a painful issue is that it rarely shows up during development, it is only once you deploy that your finely tuned app is brought down to its knees by this strange client side exception.

 

What is really happening?

Well, there is only two ways this exception can happen lets take a look:

 

1)       You use more than Max Pool Size connections (Max Pool Size default=100)

This is fairly rare in most applications, 100 concurrent connections is a very large number when you are using pooling. In my experience the only time this has been the cause of the exception above is when you open all 100 connections in a single thread as shown below:

 

 

      SqlConnection[] connectionArray = new SqlConnection[101];

      for (int i = 0; i <= 100; i++)

      {

                  connectionArray[i] = new SqlConnection(“Server=.\\SQLEXPRESS ;Integrated security=sspi;connection timeout=5”);

                  connectionArray[i].Open();

      }

Solution: Once you have determined that you are using more than 100 concurrent connections (with the same connection string) you can increase Max Pool Size.

 

2)   You are leaking connections

My definition of a leaked connection is a connection that you open but you do not Close _OR_ Dispose explicitly in your code. This covers not only the times when you forget to make the connection.Close() or Dispose() call in your code, but the much harder to catch scenarios where you _do_ call connection.Close but it does not get called! See below:

 

using System;

using System.Data;

using System.Data.SqlClient;

 

public class Repro

{

      public static int Main(string[] args)

      {

                  Repro repro = new Repro();

                  for (int i = 0; i <= 5000; i++)

                  {

                              try{ Console.Write(i+” “); repro.LeakConnections(); }

                              catch (SqlException){}

                  }

 

                  return 1;

      }

      public void LeakConnections()

      {          

                  SqlConnection sqlconnection1 = new SqlConnection(“Server=.\\SQLEXPRESS ;Integrated security=sspi;connection timeout=5”);

                  sqlconnection1.Open();

                  SqlCommand sqlcommand1 = sqlconnection1.CreateCommand();

                  sqlcommand1.CommandText = “raiserror (‘This is a fake exception’, 17,1)”;

                  sqlcommand1.ExecuteNonQuery();  //this throws a SqlException every time it is called.

                  sqlconnection1.Close(); //We are calling connection close, and we are still leaking connections (see above comment for explanation)

      }

}

 

Paste this code into visual studio and place a breakpoint in the sqlconnection1.Close(); line, it will never get called since ExecuteNonQurery throws an exception. After a short while you should see the dreaded Timeout exception, in my computer it happens at around 170 connections. This is definitely a contrived example, I am stacking the deck by lowering the connection timeout and throwing an exception every call, but when you consider moderate to heavy load on an ASP.NET application any leak is going to get you in trouble.

 

 

[EDIT: Duncan Godwin has correctly pointed out that there is a known bug with VS where this exception is thrown] 

3) You are rapidly opening or closing connections with sql debugging enabled in Visual Studio.

There is a known bug with Visual Studio 2003 and Sql Debugging, take a look at http://support.microsoft.com/default.aspx?scid=kb;en-us;830118

 

 

How to tell whether you are leaking connections in ADO.NET 2.0

 

It was very hard to figure out if you were leaking connections in v1.0 and v1.1. We have added new performance counters (see my blog below for more information) that not only kind of work (a little tongue in cheek here) but address hard to find areas like this. With ADO.NET 2.0 if you see the NumberOfReclaimedConnections performance counter go up you know that your application is leaking connections.

 

 

Beware of fixes involving the connection string! (IMPORTANT!)

Modifying the connection string can give you temporary relief from hitting this exception, so it can be very tempting. this comes at a high performance cost, you really need to fix your leak.

 

Here is a list of bad things to do to make it “kind of work” (also known as “shoot yourself in the foot”):

(Do not do) Pooling = False.

Fairly straightforward, if you turn pooling off you will never hit the timeout exception Of course you get no pooling with the performance drop that that involves. You are still leaking connections.

(Do not do) Connection Lifetime = 1;

This does not eliminate the exception altogether but it will probably come close. What you are telling us to do is to throw away from the pool any connection that has been used for more than one second (the lifetime check is done on connection.Close()). I see very little difference between this and turning pooling off, it is just plain bad. While I am talking about this connection string keyword here is a general warning. Do not use Connection Lifetime unless you are using a database cluster.

      (Do not do) Connection Timeout= 40000;

Terrible choice, you are telling us to wait forever for a connection to become available before throwing the timeout exception. Fortunately ASP.NET will do a thread abort after three minutes.

(Do not do) Max Pool Size=40000;

If you raise Max Pool Size high enough you will eventually stop getting this exception, the downside is that you will be using a much larger number of connections than what your application really needs. This does not scale well.

 

 

Solution:

You need to guarantee that the connection close _OR_ dispose gets called. The easiest way is with the “using” construct, modify your  LeakConnections() method as follows:

 

 

      public void DoesNotLeakConnections()

      {          

                  Using (SqlConnection sqlconnection1 = new SqlConnection(“Server=.\\SQLEXPRESS ;Integrated security=sspi;connection timeout=5”)) {

                              sqlconnection1.Open();

                              SqlCommand sqlcommand1 = sqlconnection1.CreateCommand();

                              sqlcommand1.CommandText = “raiserror (‘This is a fake exception’, 17,1)”;

                              sqlcommand1.ExecuteNonQuery();  //this throws a SqlException every time it is called.

                              sqlconnection1.Close(); //Still never gets called.

                  } // Here sqlconnection1.Dispose is _guaranteed_

      }

 

SqlClient Pooling Q and A:

 

Q:Why does this work?

A:The Using construct is equivalent to a Try/…/Finally{ <using object>.Dispose() ). Even when ExecuteNonQuery tries to throw out of the execution scope we guarantee that the code in the Finally block will get called.

 

Q:In the code above, wouldn’t we be calling Close and Dispose if no exception is thrown?

A:We can call Close or Dispose (or both) multiple times without any problems. Calling Close or Dispose on a Closed or Disposed connection is a no-op

 

Q:What is the difference between Close and Dispose and which one should I call?

A: You can call either one or both, they do practically the same thing.

 

Q:What do you mean by “practically the same thing”

A: Dispose will clean the connection string information from the SqlConnection and then call Close. There are no other differences, you can verify this by using reflector.

 

Q: Does connection.Dispose() remove the connection from the pool versus Close()?

A: No, see above.

 

Q: Do I also need to explicitly close an open data reader on the connection, which would require nested using statements.

A: I would recommend explicitly disposing any ado.net object that implements IDisposable. In many cases this is overkill but it is guaranteed to work (or to be a high priority bug that we need to fix yesterday) and it protects you against future changes in the framework.

 

 

Rambling out. Standard disclaimer: this post is provided AS IS and confers no rights.

 

Comments (30)

  1. Duncan Godwin says:

    Another cause is rapidly opening or closing connections with sql debugging enabled in Visual Studio. See http://support.microsoft.com/default.aspx?scid=kb;en-us;830118.

    Sql debugging disables connection pooling (which makes sense) so the same error is returned.

  2. Jerry Pisk says:

    Q: Do I also need to explicitely close an open data reader on the connection, which would require nested using statements or try/catch block (which is what I’m using right now)? Or can I just "close" the connection and not worry about whether it has an open data reader on it or not?

  3. Vasu G says:

    Does connection.Dispose() remove the connection from the pool versus Close()?

  4. Balaji says:

    We often find the following error in our event log. Seems to occur randomly atleast 4 or 5 times a day. Any ideas?

    The stored procedure call is not expensive. It just reads a record from the table.

    1) Exception Information

    *********************************************

    Exception Type: System.Data.SqlClient.SqlException

    Errors: System.Data.SqlClient.SqlErrorCollection

    Class: 10

    LineNumber: 0

    Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

    Number: -2

    Procedure: ConnectionRead (recv()).

    Server:

    State: 0

    Source: .Net SqlClient Data Provider

    TargetSite: System.Data.SqlClient.SqlDataReader ExecuteReader(System.Data.CommandBehavior, System.Data.SqlClient.RunBehavior, Boolean)

    HelpLink: NULL

    StackTrace Information

    *********************************************

    at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)

    at System.Data.SqlClient.SqlCommand.ExecuteReader()

    at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, String commandText, SqlParameter[] commandParameters, SqlConnectionOwnership connectionOwnership)

    at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteReader(SqlConnection connection, CommandType commandType, String commandText, SqlParameter[] commandParameters)

    at Deloitte.Platform.Data.SqlHelper.ExecuteReader(SqlConnection connection, CommandType commandType, String commandText, SqlParameter[] commandParameters)

    at Deloitte.Platform.Data.SqlHelper.ExecuteReader(String connectionString, CommandType commandType, String commandText, SqlParameter[] commandParameters)

    at ContentManagementHelper.Data.UserEmailDAO.GetUserInfo(String userId)

    at

  5. Angel says:

    Duncan,

    You are completelly correct. This is an ugly bug and I have edited the blog to include this regression. Thank you.

    Jerry and Vasu, I have expanded the Q&A area to include your questions, let me know if you need more information.

    Balaji,

    It definitelly looks like you are leaking connections, does this become worse with more load? If you are using Whidbey take a look at the NumberOfReclaimedConnections performance counter. Making sure that all connections are guaranteed to be closed should be the first thing you look into.

  6. Balaji says:

    Thanks. I will look into it again. I took a good look at the code a number of times before to check for leaks. No luck for me yet.

    Is there any tool / monitor that is there right now or can be done that will give the code path that opened each connection that is not closed? I know probably I’am asking for too much, but if we have such a tool, then we can turn it on in our servers and see exactly which codepath causes the connections to leak and so on.

    Just a thought. If there is something out there like this now, please let me know.

    The tool could output stuff like

    Connection ID : 34343

    —————————

    Connection String : <xxxxxx>

    ———————————-

    Status : Open

    —————-

    Opened on : 08-12-04 4:55 PM

    ———————————–

    Open stack :

    —————

    at System.Data.SqlClient.SqlCommand.ExecuteReader()

    at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, String commandText, SqlParameter[] commandParameters, SqlConnectionOwnership connectionOwnership)

    at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteReader(SqlConnection connection, CommandType commandType, String commandText, SqlParameter[] commandParameters)

    at Deloitte.Platform.Data.SqlHelper.ExecuteReader(SqlConnection connection, CommandType commandType, String commandText, SqlParameter[] commandParameters)

    at Deloitte.Platform.Data.SqlHelper.ExecuteReader(String connectionString, CommandType commandType, String commandText, SqlParameter[] commandParameters)

    at ContentManagementHelper.Data.UserEmailDAO.GetUserInfo(String userId)

    Etc

    Thanks

  7. Angel says:

    Balaji,

    In whidbey you can use the new performance counter to see if connections are leaking and I believe that you can figure out which connection is leaking from the new Tracing support added. I am trying to find out more information about how Tracing is exposed in beta 1 but so far I have nothing.

    It is very possible that a tool that checks for leaked connections could be made, I am going to have to look into it in more detail.

  8. Bill says:

    Really great post – it needs to be on MSDN 😉

  9. Bill Vaughn says:

    Ah, the number 2 most-frequent-cause for this timeout is when too much work is demanded of the server. For example, in an ASP environment, if you get too busy (too many hits), the amount of processing that needs to be done in order for a connection to be free by the time another user requests one is too high. In other words, if an ASP instance is blocked or held up by processing limitations, it must hold the connection open until it’s done. If another instance is opened, another connection has to be added to the pool (the first is busy). This is perfectly normal, but can only go on so long. When the processor capacity matches the demand, the number of pooled connections remains relatively flat. When the demand increases beyond capacity, it creaps up until it finally exhausts the pool capacity. If the server is capable of handling many operations at once (most are with JET being the notable exception), it can handle dozens of working connections. However, there are limits…

  10. John Saunders says:

    I’m a bit confused about this:

    —-

    Q:What do you mean by “practically the same thing”

    A: Dispose will clean the connection string information from the SqlConnection and then call Close. There are no other differences, you can verify this by using reflector.

    —-

    When you say "clean the connection string information", do you mean it does "<SqlConnection>.ConnectionString = null", or do you mean it clears internal state?

    Also, under what circumstances would I want to clear the connection string information or to not clear it?

  11. Angel says:

    John, yes exactly. Dispose will null out the ConnectionString and then call close. That is all. I will update the blog to make this clear.

  12. Angel says:

    Forgot to address your second question. The only difference would be that code like this:

    con.open

    con.close

    con.open

    would work, where this

    con.open

    con.dispose.

    con.open

    would not since the connection string has been cleared. There is nothing stopping you from doin the following though:

    con.open

    con.dispose

    con.ConnectionString=<valid connection string>

    con.open

  13. Richard says:

    I’m trying to find leaking connections in some code that I inherited. The code is using Microsoft Data Access Application Block for .NET Version 2.0’s SQLHelper.cs, and contains this code, which looks like it requires the caller to close the connection. Unfortunately, there isn’t a way of getting the connection from the DataReader (is there?). Maybe I’m wrong. Maybe when the DataReader is disposed it closes the connection (but I doubt it). Anyone got any ideas?

    public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)

    {

    if( connectionString == null || connectionString.Length == 0 ) throw new ArgumentNullException( "connectionString" );

    SqlConnection connection = null;

    try

    {

    connection = new SqlConnection(connectionString);

    connection.Open();

    // Call the private overload that takes an internally owned connection in place of the connection string

    return ExecuteReader(connection, null, commandType, commandText, commandParameters,SqlConnectionOwnership.Internal);

    }

    catch

    {

    // If we fail to return the SqlDatReader, we need to close the connection ourselves

    if( connection != null ) connection.Close();

    throw;

    }

               

    }

  14. Richard says:

    Apologies. I think I’ve worked it out now… ExecuteReader is being called with SqlConnectionOwnership.Internal, which means it uses

    dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

    which does mean that the connection is closed when the DataReader is closed. Sorry.

  15. rape stories says:

    Your article is prety nice. It’s a pity that i didn’t see it more later.

  16. Jeff Stong says:

    (or, how to load test the connection pool without breaking a sweat)

    Recently, I encountered a question…

  17. Erno de Weerd recently posted a blog entry on how SqlConnection.Dispose removes the connection from the

  18. Error: System.Data.SqlClient.SqlException: Timeout expired

  19. Aunque en desarrollo using sólo será una instrucción , cuando hagas deployment verás el poder de using

  20. Aunque en desarrollo using sólo será una instrucción , cuando hagas deployment verás el poder de using

  21. We get a lot of queries from developers around the following error while they are using ADO.NET &quot;Timeout

  22. We get a lot of queries from developers around the following error while they are using ADO.NET &quot;Timeout