Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool.
The Scenario:
Sometimes you might get the following errors letting you know that you have exhausted the connection pool even though you are under the impression that this should not be the case.
--> In .Net 1.1
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.
at System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction)
at System.Data.SqlClient.SqlConnection.Open()
--> In .Net 2.0
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.
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
Usually this happens because you in the code close, for example, a DataReader, but you do not close its associated connection.
Again, using my preferred way, I’ll show this by example. In order to provoke this error, create a new Console application in Visual Studio:
static void Main(string[] args)
{
string connString = @"Data Source=<your server>;Initial Catalog=Northwind;Integrated Security=True; Max Pool Size=20; Connection Timeout=10";
try
{
for (int i = 0; i < 50; i++)
{
// Create connection, command and open the connection
SqlConnection sc = new SqlConnection(connString);
SqlCommand sCmd = new SqlCommand("SELECT * FROM Shippers", sc);
sc.Open();
// Print info
Console.WriteLine("Connections open: {0}", i.ToString());
// This will cause the error to show.
SqlDataReader sdr = sCmd.ExecuteReader();
sdr.Close();
// Replacing the two rows above with these will remove the error
//SqlDataReader sdr = sCmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
//sdr.Close();
// -- or --
// Explicity close the connection
//sc.Close();
}
// -- or --
// Run all in a Using statement (in this case, replace the whole for loop with the loop below.).
//for (int i = 0; i < 50; i++)
//{
// using (SqlConnection sc = new SqlConnection(connString))
// {
// SqlCommand sCmd = new SqlCommand("SELECT * FROM Shippers", sc);
// sc.Open();
// Console.WriteLine("Conns opened " + i.ToString());
// SqlDataReader sdr = sCmd.ExecuteReader();
// sdr.Close();
// }
//}
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
Run this, and you should hit the error. Basically, the reason for the error is in the exception message, the connection pool is exhausted.
In the connection string I have set the Max Pool size to 20 and the connection timeout to 10 seconds in order to get the error quicker.
The default is 100 for Max Pool Size and 15 seconds for the connection timeout.
Normally this shouldn’t happen, but as usual, if it didn’t happen, I wouldn’t write about it.
The code above is made up but it shows one common reason for this, that is that a connection is opened and something is done with it, but then the connection is not closed.
When seeing this error, the code usually opens the connection in one place, the operation is done in another place, and then the closing of the connection is made in a third place.
What could happen in that scenario is that something goes wrong in the operational method so that the call to close is never made. When this happens, the application will
create a new connection instead of picking one from the pool, the connection will only go into the pool when it is closed.
The general recommendation is to Open/Execute/Close as soon as possible.
Getting a connection from the pool is fast and not expensive, so there is no need for keeping the connection open any longer than necessary.
In the code above, there are three solutions depending on what you would like to do.
.1 If you want the connection associated with the SqlDataReader to be closed when the reader is closed, execute it with the CommandBehavior.CloseConnection enumeration.
SqlDataReader sdr = sCmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
.2 Explicitly close the connection when done.
sc.Close();
.3 Use the connection in a Using block, this guarantees that the system disposes the connection (and closes it) when the code exits the block.
Replace the whole FOR loop in the code above with this:
for (int i = 0; i < 50; i++)
{
using (SqlConnection sc = new SqlConnection(connString))
{
SqlCommand sCmd = new SqlCommand("SELECT * FROM Shippers", sc);
sc.Open();
Console.WriteLine("Conns opened " + i.ToString());
SqlDataReader sdr = sCmd.ExecuteReader();
sdr.Close();
}
}
I would personally recommend the use of 3.
References:
".NET Framework Class Library - SqlConnection ConnectionString Property "
https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx
".NET Framework Class Library - CommandBehavior Enumeration"
https://msdn.microsoft.com/en-us/library/system.data.commandbehavior.aspx
"C# Language Reference - using Statement (C# Reference)"
https://msdn.microsoft.com/en-us/library/yh598w02.aspx
On pooling:
"Connection Pooling for the .NET Framework Data Provider for SQL Server"
https://msdn2.microsoft.com/en-us/library/8xx3tyca(VS.80).aspx
"The .NET Connection Pool Lifeguard"
https://msdn2.microsoft.com/en-us/library/aa175863(SQL.80).aspx
Comments
Anonymous
January 14, 2009
Today I thought I give you some tips on how to prepare for a SQL Developer / Connectivity case. SometimesAnonymous
January 05, 2016
I seem to do what you suggest but I still get that error. Dim CN As New SqlConnection Dim ConnCmd As SqlCommand Dim strSQL As String = "" Dim specialServiceCtr As Int16 = 0 Dim iWeight As Integer = 0 Dim myReader As System.Data.SqlClient.SqlDataReader If CN.State = ConnectionState.Closed Then If strCNstring <> "" Then CN.ConnectionString = strCNstring CN.Open() Else Throw New Exception("Please set Local Connection string first.") End If End If strSQL = "Select lines from order" ConnCmd = New SqlCommand(strSQL, CN) ConnCmd.Parameters.AddWithValue("@1", sOrdType) ConnCmd.Parameters.AddWithValue("@2", sOrdNo.ToString.Trim) ConnCmd.Parameters.AddWithValue("@3", strLocOrVend.Trim) myReader = ConnCmd.ExecuteReader(CommandBehavior.Default) While myReader.Read packageCtr += 1 End While myReader.Close() ConnCmd.Dispose() CN.Close()