Connection Pool Exhaustion: Reproducing the Timeout and What SQL Server Sees

The application team sends over a stack trace. It says “Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool.” They want to know what’s wrong with the database. CPU is low, nothing is blocked, and the error log is clean. The database may be fine. That message comes from the client driver, and in my test the attempt that failed didn’t create a session on SQL Server.

A woman DBA traces abandoned connection cables back to an application while robots wait at an empty rack of connection hooks.

I wanted to see the whole sequence from both sides, so I wrote a small PowerShell test that leaks connections until the pool runs out. Then I ran the same workload with proper disposal and compared what the server saw.

Where the Error Comes From

ADO.NET keeps a pool of physical connections for each connection configuration. When code calls Open, the pooler hands out an idle connection from the matching pool if it has one. When code calls Close or Dispose, the connection goes back to the pool instead of being closed. A pool grows up to Max Pool Size, which defaults to 100. When the pool is full and every connection is checked out, the next request waits. If no connection comes back before the timeout (15 seconds by default), the driver throws an exception.[1] The newer Microsoft.Data.SqlClient documentation says the wait is controlled by Connect Timeout.[2] My test used the older System.Data.SqlClient, and there too the wait followed Connect Timeout: with it set to 5, the failing open gave up after about 5 seconds.

In the test below, SQL Server sees the ten connections the pool holds. The request that’s waiting never reaches it.

The Leaking Test

I ran this on one workstation against SQL Server 2025 (17.0.5015.4, Enterprise Developer), over shared memory. The client was Windows PowerShell 5.1 with System.Data.SqlClient from .NET Framework 4.8. Each test ran in its own powershell.exe process, so the process exit closed anything the test left open. Every connection string used an Application Name starting with sat_pool_, which made the test sessions easy to find.

The leaking version sets Max Pool Size=10 and Connect Timeout=5, then opens connections in a loop and never closes them. On the third pass it also starts a transaction and doesn’t commit it, to imitate a forgotten COMMIT:

The full script wraps the loop in try/catch to time the failure. The first run printed this:

Ten opens worked, each on a new session. The eleventh waited for the full Connect Timeout and failed. I ran it three times. The failing open took 5,019, 5,029 and 5,010 ms, and the whole run took about 5.2 seconds from the first open to the exception. The exception is an InvalidOperationException from the pool, not a SqlException from the server.

What the Server Sees

While the leaked connections were still held, a separate non-pooled connection ran this query. The program_name filter keeps it to the test sessions. On a real server, drop that filter and look for the groups with the most sessions:

Ten sessions, all sleeping, one with an open transaction. That matches Max Pool Size. The eleventh attempt didn’t create a session. Both host_name and program_name come from the client. The documentation warns that the client supplies the workstation name and can send an inaccurate value.[3] I still use them to tie a group of sessions to an application.

To see each session, join to sys.dm_exec_connections, which has one row per connection to the instance. If the application uses MARS (Multiple Active Result Sets), the view also returns an extra row for each MARS logical session, with net_transport set to Session.[4] My test didn’t use MARS.

Every session ran one request within milliseconds of connecting and then did nothing. On a server with a leak, I’d expect last_request_end_time on these sessions to stay old while new ones keep arriving, until the count reaches the pool limit. Session 98 is the one to look at first. A sleeping session with open_transaction_count above zero holds that transaction open on the server, and the client code no longer has a reference it could use to commit it. Adding AND [s].[open_transaction_count] > 0 to the detail query finds those sessions directly. It also returns pooled connections that the code disposed with a transaction still open, until the pool resets them (more on that below). So I’d check last_request_end_time too. An old value means the transaction has sat idle for that long.

The Fixed Version

The fix is to return every connection to the pool on every code path. In C# that’s a using block. In PowerShell it’s try/finally:

The workload is the same, including the uncommitted transaction on pass 3. The two runs compared like this:

Measure Leaking loop Disposing loop
Iterations attempted 11 15, then 1,000
Iterations completed 10 15, then 1,000
Server sessions created 10 1
Distinct @@SPID values 10 1
Time to failure 5,010 to 5,029 ms no failure
Elapsed time about 5.2 s to the exception 1,274 ms for 1,000 iterations

Each timing covers the open plus one query. In these runs, the first open plus query took 88 to 108 ms. In the leaking loop, iterations 2 to 10 each made a new physical connection, and each open plus one query took 4 to 12 ms over shared memory. In the disposing loop, every later iteration reused the same session, the ones I printed took 0 to 1 ms, and 1,000 iterations took 1,274 ms in that run. The leaking loop showed no slowdown before the eleventh open, which waited the full 5 seconds.

Closing a Connection Doesn’t End the Session

The documentation treats Close and Dispose the same way for pooling.[1] I used Dispose(). After the disposing loop finished, the server still showed one sat_pool_fixed session, sleeping. That’s the pool keeping its physical connection for the next Open. It went away when I called SqlConnection.ClearAllPools(). Without that call, the pooler removes an idle connection after about 4 to 8 minutes.[1] Sleeping sessions from an app server are normal with pooling. I’d compare the count with Max Pool Size.

The pool also resets the session state before it hands a connection out again.[2] To see when that happens, I ran a separate test with an Extended Events session on rpc_completed, sql_batch_completed and login. The first use created a temp table, set CONTEXT_INFO and started a transaction. Then I disposed the connection and opened a new one from the same pool:

Check First use After Dispose(), before reuse Second use
@@SPID 85 85 (sleeping) 85
@@TRANCOUNT / open_transaction_count 1 1 0
Temp table exists yes not checked no
CONTEXT_INFO() 0x5A… not checked NULL

I set CONTEXT_INFO to 0x5A and converted it to a short string for the output, so the table shows only the first byte.

The trace recorded an rpc_completed event for sp_reset_connection on session 85 in the same millisecond as the second batch, not when Dispose() ran. It also recorded a second login event with is_cached set to true. The event’s column description for is_cached is “Reuse of cached connection from connection pool or new connection?” Microsoft’s documentation for Close opens with “The Close method rolls back any pending transactions.” A note further down says that with pooling, a transaction started with Transact-SQL is rolled back when the connection is reset.[5] My trace shows that reset happening on the next Open, not on Dispose(). Until then, the server showed a sleeping session with an open transaction that my code had already disposed. Any locks that transaction holds may stay held until the next reuse or until the pool removes the connection. I didn’t test lock retention for this post.

How Pools Multiply

The pool key is the connection string text. It has to be an exact match, so the same settings in a different keyword order get a separate pool. With integrated security, each Windows identity also gets its own pool.[1] I opened and disposed one connection with each of six strings, in one process:

Connection string Session
Base string, Application Name=sat_pool_keys_a 85
Same text again 85
Same keywords, Application Name moved first 87
Extra space after one semicolon 88
Connect Timeout=6 instead of 5 89
Application Name=sat_pool_keys_b 90

Six opens made five server sessions, and all five stayed open after Dispose(). Only the identical string reused a session. Every variant could hold up to Max Pool Size sessions of its own. I tested with one Windows account, so I didn’t measure the per-identity split. That part comes from the documentation only.

The Microsoft.Data.SqlClient documentation recommends one canonical connection string per workload. It also warns against per-request values in Application Name or Workstation ID. Before raising Max Pool Size, it says to confirm that the server can handle that value multiplied by every pool in every application instance.[2] Pools are kept per process and per application domain, as well as per connection string.[1] As a worked example, four web servers, each with one worker process that builds three slightly different strings, with the default limit of 100, can open up to 1,200 sessions.

Limits of This Test

I tested on one workstation, over shared memory, with System.Data.SqlClient on .NET Framework. I didn’t test Microsoft.Data.SqlClient, its V2 pool, or a remote server. My leaking loop ran for about 5 seconds, and all ten leaked sessions were still open when the eleventh open failed. The documentation says connections that aren’t closed might not be returned to the pool.[1] In a long-running application, garbage collection may reclaim some leaked connections, which could make the failure intermittent. The Microsoft.Data.SqlClient diagnostic counters include one for reclaimed connections that the code didn’t dispose.[2] I didn’t test that.

After the last test I checked the instance again. No session with a program_name starting with sat_pool_ remained.

Where I’d Start

If I saw this error, I’d start with the grouping query and compare the largest count with the application’s Max Pool Size. A count at the limit with old last_request_end_time values would point at a leak. Many groups for one application, split by login or by Application Name, would point at fragmentation. Keyword-order differences don’t show up in the DMVs, though. In my test, the four sat_pool_keys_a pools looked like one group of four sessions. A pool timeout can also come from slow queries or blocking that keeps every connection busy,[2] so I’d check sys.dm_exec_requests for running sessions before blaming the code. The Microsoft.Data.SqlClient documentation advises against raising Max Pool Size until every connection is disposed on every path.[2] Raising it on a leaking app just lets more sessions pile up before the same error.

I mentioned a pool exhaustion problem briefly in Incident Response: Root Cause Analysis with an AI Partner. That post covered the diagnosis. Here I wanted the failure on my own instance.

Related: THREADPOOL Waits: Reproducing Worker Thread Exhaustion in SQL Server.

If you’ve tracked down a pool leak in a way I didn’t cover here, I’d like to hear about it. Find me on Bluesky or LinkedIn.

References

  1. SQL Server Connection Pooling (ADO.NET) - Microsoft Learn. Describes pool creation by exact connection string, per process, per application domain and per Windows identity, the default maximum of 100, queued requests that fail at the timeout, the advice to always close connections, and removal of idle connections after about 4 to 8 minutes. ↩
  2. SQL Server connection pooling with Microsoft.Data.SqlClient - Microsoft Learn. States that Connect Timeout sets how long Open waits for a pooled connection, that the pool resets session state on reuse, how pool keys fragment, and that the server must handle Max Pool Size multiplied by every pool in every application instance. Lists a diagnostic counter for reclaimed connections that code didn’t dispose, and says a pool timeout can mean a leak, slow queries or blocked transactions. ↩
  3. sys.dm_exec_sessions (Transact-SQL) - Microsoft Learn. Defines host_name, program_name, open_transaction_count and last_request_end_time, and notes that the client supplies the workstation name. ↩
  4. sys.dm_exec_connections (Transact-SQL) - Microsoft Learn. Returns one row per connection to the instance, with connect_time and net_transport, and returns Session in net_transport for each additional connection associated with a MARS logical session. ↩
  5. SqlConnection.Close Method (System.Data.SqlClient) - Microsoft Learn, .NET Framework 4.8. States that Close rolls back pending transactions, and that pending transactions started using Transact-SQL or BeginTransaction are automatically rolled back when the connection is reset if connection pooling is enabled. ↩