"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.（这里，如果数据库连接字符串有有数据库的登录用户名和密码，也会被dump出来，嘿嘿~~~YY...）
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.”
