Connection Pooling and the "Timeout expired" exception FAQ

re: Connection Pooling and the "Timeout expired" exception FAQ

Duncan Godwin — Wed, 25 Aug 2004 18:22:00 GMT

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.

re: Connection Pooling and the "Timeout expired" exception FAQ

Jerry Pisk — Wed, 25 Aug 2004 19:17:00 GMT

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?

re: Connection Pooling and the "Timeout expired" exception FAQ

Vasu G — Wed, 25 Aug 2004 19:37:00 GMT

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

re: Connection Pooling and the "Timeout expired" exception FAQ

Balaji — Wed, 25 Aug 2004 20:06:00 GMT

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

re: Connection Pooling and the "Timeout expired" exception FAQ

Angel — Wed, 25 Aug 2004 20:27:00 GMT

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.

re: Connection Pooling and the "Timeout expired" exception FAQ

Balaji — Thu, 26 Aug 2004 15:04:00 GMT

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

re: Connection Pooling and the "Timeout expired" exception FAQ

Angel — Thu, 26 Aug 2004 17:19:00 GMT

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.

re: Connection Pooling and the "Timeout expired" exception FAQ

Bill — Fri, 27 Aug 2004 01:05:00 GMT

Really great post - it needs to be on MSDN ;-)

The Real Deal on Connection Management

Bill's House O Insomnia — Fri, 27 Aug 2004 04:07:00 GMT

Connection Pool Timeouts

OdeToCode Link Blog — Fri, 27 Aug 2004 07:09:00 GMT

re: Connection Pooling and the "Timeout expired" exception FAQ

Bill Vaughn — Wed, 15 Sep 2004 20:01:00 GMT

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

re: Connection Pooling and the "Timeout expired" exception FAQ

John Saunders — Thu, 23 Sep 2004 18:40:00 GMT

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?

re: Connection Pooling and the "Timeout expired" exception FAQ

Angel — Thu, 23 Sep 2004 18:43:00 GMT

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.

re: Connection Pooling and the "Timeout expired" exception FAQ

Angel — Thu, 23 Sep 2004 18:46:00 GMT

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

Disposal Anxiety

K. Scott Allen's Blog — Mon, 08 Nov 2004 02:51:00 GMT

Disposal Anxiety

K. Scott Allen's Blog — Mon, 08 Nov 2004 02:45:00 GMT

Closing vs. Disposing of Exceptions

Bill's House O Insomnia — Sun, 14 Nov 2004 08:32:00 GMT

re: Connection Pooling and the "Timeout expired" exception FAQ

Richard — Fri, 05 May 2006 13:21:12 GMT

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;
}

}

re: Connection Pooling and the "Timeout expired" exception FAQ

Richard — Fri, 05 May 2006 13:37:09 GMT

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.

re: Connection Pooling and the "Timeout expired" exception FAQ

rape stories — Tue, 20 Jun 2006 06:47:01 GMT

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

Testing connection pooling and "timeout expired" exceptions

Jeff Stong — Fri, 01 Sep 2006 18:25:51 GMT

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

Recently, I encountered a question...

Never-ending story of Close versus Dispose

Alex Thissen Weblog Build 1.15.10.1971 — Tue, 19 Dec 2006 17:58:02 GMT

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

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

BizTalk Blog by Chris Han — Thu, 28 Jun 2007 16:49:16 GMT

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

Mao’s Blog » Blog Archive » connection pooling in .Net

Mao’s Blog » Blog Archive » connection pooling in .Net — Tue, 18 Sep 2007 12:22:39 GMT

PingBack from http://www.mauriziopapini.eu/wordpress/?p=9

ADO.NET: tu conexion no se cierra con Close?, el poder de using

SergioTarrillo's RichWeblog — Thu, 17 Jan 2008 17:51:35 GMT

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

ADO.NET: tu conexion no se cierra?, el poder de using

ASP.NET Espanol Blogs — Fri, 18 Jan 2008 19:53:13 GMT

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

Indications that we are leaking connections

Digging the Unkown — Sun, 02 Mar 2008 11:55:18 GMT

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

Indications that we are leaking connections

Noticias externas — Sun, 02 Mar 2008 12:28:07 GMT

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

MSDN Blog Postings » Indications that we are leaking connections

MSDN Blog Postings » Indications that we are leaking connections — Sun, 02 Mar 2008 13:32:12 GMT

PingBack from http://msdnrss.thecoderblogs.com/2008/03/02/indications-that-we-are-leaking-connections/

Windows Server 2003 : Problema con l’app Pool: The timeout period elap..

Windows Server 2003 : Problema con l’app Pool: The timeout period elap.. — Sun, 22 Feb 2009 14:57:45 GMT

PingBack from http://www.winnyspot.com/windows-server-2003-problema-con-lapp-pool-the-timeout-period-elap

Connection Pooling and the "Timeout expired" exception FAQ

re: Connection Pooling and the "Timeout expired" exception FAQ

re: Connection Pooling and the "Timeout expired" exception FAQ

re: Connection Pooling and the "Timeout expired" exception FAQ

re: Connection Pooling and the "Timeout expired" exception FAQ

re: Connection Pooling and the "Timeout expired" exception FAQ

re: Connection Pooling and the "Timeout expired" exception FAQ

re: Connection Pooling and the "Timeout expired" exception FAQ

re: Connection Pooling and the "Timeout expired" exception FAQ

The Real Deal on Connection Management

Connection Pool Timeouts

re: Connection Pooling and the "Timeout expired" exception FAQ

re: Connection Pooling and the "Timeout expired" exception FAQ

re: Connection Pooling and the "Timeout expired" exception FAQ

re: Connection Pooling and the "Timeout expired" exception FAQ

Disposal Anxiety

Disposal Anxiety

Closing vs. Disposing of Exceptions

re: Connection Pooling and the "Timeout expired" exception FAQ

re: Connection Pooling and the "Timeout expired" exception FAQ

re: Connection Pooling and the "Timeout expired" exception FAQ

Testing connection pooling and &quot;timeout expired&quot; exceptions

Never-ending story of Close versus Dispose

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

Mao’s Blog » Blog Archive » connection pooling in .Net

ADO.NET: tu conexion no se cierra con Close?, el poder de using

ADO.NET: tu conexion no se cierra?, el poder de using

Indications that we are leaking connections

Indications that we are leaking connections

MSDN Blog Postings » Indications that we are leaking connections

Windows Server 2003 : Problema con l’app Pool: The timeout period elap..

Testing connection pooling and "timeout expired" exceptions