"System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool."

"System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool."

  • Comments 1

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

http://blogs.msdn.com/b/spike/archive/2008/08/25/timeout-expired-the-timeout-period-elapsed-prior-to-obtaining-a-connection-from-the-pool.aspx

 

 

Leave a Comment
  • Please add 8 and 3 and type the answer here:
  • Post
  • hi, when i try to check my pool, i got:

    0:000> !dumpheap -stat -type System.Data.ProviderBase.DbConnectionPool

    CLRDLL: Loaded DLL C:\Windows\Microsoft.NET\Framework\v4.0.30319\mscordacwks.dll

    Failed to load data access DLL, 0x80004005

    Verify that 1) you have a recent build of the debugger (6.2.14 or newer)

               2) the file mscordacwks.dll that matches your version of clr.dll is

                   in the version directory

               3) or, if you are debugging a dump file, verify that the file

                   mscordacwks_<arch>_<arch>_<version>.dll is on your symbol path.

               4) you are debugging on the same architecture as the dump file.

                   For example, an IA64 dump file must be debugged on an IA64

                   machine.

    You can also run the debugger command .cordll to control the debugger's

    load of mscordacwks.dll.  .cordll -ve -u -l will do a verbose reload.

    If that succeeds, the SOS command should work on retry.

    If you are debugging a minidump, you need to make sure that your executable

    path is pointing to clr.dll as well.

    how to solve this error?

Page 1 of 1 (1 items)