I have an application that takes data from a DB2 database (our primary database), and inserts it into SQL Server (a reporting-type database) as part of one of our processes.
Last night, we got this error from opening a SQL Server connection:
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.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.Open()
This happened a few times, but afterwards our DB2-based queries started timing out, too (or at least, we started hitting the WCF timeout that has been established).
I think I know the reason for the SQL Server connection pool issue, but I would not have guessed that the SQL Server issues would have caused issues for the DB2 side (I'm talking about completely separate processes timing out from the DB2 server).
My question then, is: does .NET have a connection pool per ADO.NET provider (which I would have expected), or is it for all connections (which this error seems to indicate)?
If it makes a difference, we are using .NET's TransactionScope to keep the transactions between DB2 and SQL Server in sync. This answer seems to indicate that the connections used in a TransactionScope are not released until the end of a scope.
