"System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool."
Another post on the following exception:
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()
Basically this means that the connection pool is full and that all connections are in use. Which we can see from the text in the exception J
However, sometimes you may not have the source code but still need to figure out what the connection pool settings and state is.
So, the premise here is that you have an application that sometimes fails with the exception above and you do not have the source but you wish
to figure out what the max and min pool sizes are and what the current number of connections in the pool is.
Start by creating a simple command prompt application that connects to a SQL Server (code below is to provoke the exception and do not reflect
any recommendation on how to program database connections).
namespace PoolExhaust
{
class Program
{
static void Main(string[] args)
{
string connString = @"Data Source=<your server>;Initial Catalog=<your database>;Integrated Security=True; Max Pool Size=20; Connection Timeout=10";
try
{
for (int i = 0; i < 50; i++)
{
SqlConnection sc = new SqlConnection(connString);
SqlCommand sCmd = new SqlCommand("SELECT * FROM <your table>", sc);
sc.Open();
Console.WriteLine("Connections open: {0}", i.ToString());
SqlDataReader sdr = sCmd.ExecuteReader();
sdr.Close();
}
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
}
}
Build it and run it to confirm that you get the exception.
Now, since we do not have the source code (in theory) we should generate a dump. Assuming that we are on Windows 2008 or 7 you can simply start the Task Manager and then run the application again.
When it is running, right click the application in Task Manager and select “Create Dump File”. Take note of where it is stored, should be in the C:\Users\...
Open the dump in WinDbg and load the SOS extension suitable for your application.
0:000> .load C:\Windows\Microsoft.NET\Framework64\v4.0.30319\sos.dll
Now, what we want to know is what the max and min pool size is for the connection pool and what the current number of connections in the pool is.
So we will start with getting the DbConnectionPool.
0:000> !dumpheap -stat -type System.Data.ProviderBase.DbConnectionPool
total 0 objects
Statistics:
MT Count TotalSize Class Name
…
000007fee3653dc0 1 176 System.Data.ProviderBase.DbConnectionPool
…
Then use the MT to get the address:
0:000> !dumpheap -mt 000007fee3653dc0
Address MT Size
0000000002db24c0 000007fee3653dc0 176
and then dump the address:
0:000> !do 0000000002db24c0
Name: System.Data.ProviderBase.DbConnectionPool
MethodTable: 000007fee3653dc0
EEClass: 000007fee34d26f8
Size: 176(0xb0) bytes
File: C:\Windows\Microsoft.Net\assembly\GAC_64\System.Data\v4.0_4.0.0.0__b77a5c561934e089\System.Data.dll
Fields:
MT Field Offset Type VT Attr Value Name
000007fee5e9c610 4001579 88 System.Int32 1 instance 190000 _cleanupWait
000007fee3652e80 400157a 8 ...ctionPoolIdentity 0 instance 0000000002db24a0 _identity
000007fee36535f0 400157b 10 ...ConnectionFactory 0 instance 0000000002d8dfb8 _connectionFactory
000007fee3653700 400157c 18 ...nnectionPoolGroup 0 instance 0000000002db0bb0 _connectionPoolGroup
000007fee36546d0 400157d 20 ...nPoolGroupOptions 0 instance 0000000002db0b88 _connectionPoolGroupOptions
000007fee3b82610 400157e 28 ...nPoolProviderInfo 0 instance 0000000000000000 _connectionPoolProviderInfo
…
000007fee5e9c610 400158e 98 System.Int32 1 instance 20 _totalObjects
…
Here you will see _totalObjects. This is the number of connections in the pool, which happens to be 20 in this case. Then dump the _connectionPoolGroupOptions:
0:000> !do 0000000002db0b88
Name: System.Data.ProviderBase.DbConnectionPoolGroupOptions
MethodTable: 000007fee36546d0
EEClass: 000007fee34f5620
Size: 40(0x28) bytes
File: C:\Windows\Microsoft.Net\assembly\GAC_64\System.Data\v4.0_4.0.0.0__b77a5c561934e089\System.Data.dll
Fields:
MT Field Offset Type VT Attr Value Name
000007fee5e9d440 40015d8 14 System.Boolean 1 instance 1 _poolByIdentity
000007fee5e9c610 40015d9 8 System.Int32 1 instance 0 _minPoolSize
000007fee5e9c610 40015da c System.Int32 1 instance 20 _maxPoolSize
…
Which clearly shows you the max and min pool sizes. In this case we will soon get the mentioned exception since we have 20 objects in the pool and the max is 20.
Now, if you wish to find out the connection string for the connections in the dump. Simply dump the SqlConnections
0:000> !dumpheap -type System.Data.SqlClient.SqlConnection
Address MT Size
0000000002d8dfb8 000007fee36532a8 64
0000000002db0360 000007fee36542b8 184
…
00000000030e7928 000007fee364fba8 104
Pick one and dump that and then check the _userConnectionOptions.
0:000> !do 00000000030e7928
Name: System.Data.SqlClient.SqlConnection
MethodTable: 000007fee364fba8
EEClass: 000007fee34d1c38
Size: 104(0x68) bytes
File: C:\Windows\Microsoft.Net\assembly\GAC_64\System.Data\v4.0_4.0.0.0__b77a5c561934e089\System.Data.dll
Fields:
MT Field Offset Type VT Attr Value Name
…
000007fee36543a8 4001775 38 ...ConnectionOptions 0 instance 0000000002db0360 _userConnectionOptions
000007fee3653700 4001776 40 ...nnectionPoolGroup 0 instance 0000000002db0bb0 _poolGroup
…
0:000> !do 0000000002db0360
Name: System.Data.SqlClient.SqlConnectionString
MethodTable: 000007fee36542b8
EEClass: 000007fee34f53b0
Size: 184(0xb8) bytes
File: C:\Windows\Microsoft.Net\assembly\GAC_64\System.Data\v4.0_4.0.0.0__b77a5c561934e089\System.Data.dll
Fields:
MT Field Offset Type VT Attr Value Name
000007fee5e96728 4000c17 8 System.String 0 instance 0000000002d8de10 _usersConnectionString
…
As we can see, luckily for us, this have a _usersConnectionString. Simply dump that:
0:000> !do 0000000002d8de10
Name: System.String
MethodTable: 000007fee5e96728
EEClass: 000007fee5a1ed68
Size: 256(0x100) bytes
File: C:\Windows\Microsoft.Net\assembly\GAC_64\mscorlib\v4.0_4.0.0.0__b77a5c561934e089\mscorlib.dll
String: Data Source=<server>;Initial Catalog=<database>;Integrated Security=True; Max Pool Size=20; Connection Timeout=10
And you will see the connection string. Which again confirms what we found on the connection pool object.
So, this post is about how to find these values when you do not have the source but the possibility to take a dump on the process.
For reasons, and possible, and solutions. See a previous post here:
“Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool.”