Improve error reporting when an application fails to obtain a connection from the pool

[David-Engel]

Captured from a discussion with a support engineer.

Is your feature request related to a problem? Please describe.

When an application uses connection pooling and is running at the pool's capacity (max connections), there are instances where the error from the connection pool can hide the underlying cause of a failure to obtain a connection from the pool.

Obtaining a connection from the pool is done in the context of a timeout. If the request times out, the pool throws a PooledOpenTimeout exception: 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. This error could be because they are truly using all the pool slots. It could also be that their application is "leaking" connections (not properly closing/disposing connection objects or calling thread abort, which results in an orphaned connection object) and hitting max connections.

Other types of failures may occur due to connection errors like TCP remote host refused the connection. These types of failures can be hidden because retries during the timeout effectively hide the exceptions until the timeout error is thrown. These failures comprise a majority of the support cases that come in for the timeout error. The aspect that makes these painful is that SqlClient isn't capturing and surfacing the inner exception from the connection failure. It takes a memory dump at just the right time to get those details. If it could include the last connection exception as an inner exception on the PooledOpenTimeout exception, it would make connectivity troubleshooting significantly easier in these scenarios.

Describe the solution you'd like

During connection creation in a connection pool, capture the last connection exception (if a physical connection attempt was made) and surface that as the inner exception on PooledOpenTimeout if timeout occurs before a successful connection is made.

[paulmedynski]

It might make sense for connection open retries to capture a list of all of the transient failures, and if the open ultimately fails (for a permanent error or timeout), provide the list of transient errors. A sort of ledger of what happened behind the Open() call to help diagnose failures.