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.

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:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
$cs = 'Server=.\SQL2025;Integrated Security=SSPI;Application Name=sat_pool_leak;Max Pool Size=10;Connect Timeout=5' for ($i = 1; $i -le 15; $i++) { $sw = [System.Diagnostics.Stopwatch]::StartNew() $conn = New-Object System.Data.SqlClient.SqlConnection $cs $conn.Open() $cmd = $conn.CreateCommand() $cmd.CommandText = if ($i -eq 3) { 'BEGIN TRANSACTION; SELECT @@SPID;' } else { 'SELECT @@SPID;' } $spid = $cmd.ExecuteScalar() "iteration {0,2}: open+query {1,6} ms, spid {2}" -f $i, $sw.ElapsedMilliseconds, $spid # No Close() and no Dispose(). } |
The full script wraps the loop in try/catch to time the failure. The first run printed this:
|
1 2 3 4 5 6 7 8 |
iteration 1: open+query 106 ms, spid 93 iteration 2: open+query 5 ms, spid 97 iteration 3: open+query 4 ms, spid 98 ... iteration 10: open+query 5 ms, spid 105 iteration 11: FAILED after 5019 ms (total elapsed 5184 ms) Exception type: System.InvalidOperationException Message: 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. |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
USE [master]; SELECT [s].[host_name] , [s].[program_name] , [s].[login_name] , COUNT_BIG(1) AS [sessions] , SUM(CASE WHEN [s].[status] = N'sleeping' THEN 1 ELSE 0 END) AS [sleeping] , SUM([s].[open_transaction_count]) AS [open_tran] , MIN([s].[last_request_end_time]) AS [oldest_last_request_end] , MAX([s].[last_request_end_time]) AS [newest_last_request_end] FROM [sys].[dm_exec_sessions] AS [s] WHERE [s].[program_name] LIKE N'sat[_]pool[_]%' GROUP BY [s].[host_name] , [s].[program_name] , [s].[login_name] ORDER BY [sessions] DESC; |
|
1 2 3 |
host_name program_name login_name sessions sleeping open_tran oldest_last_request_end newest_last_request_end --------- ------------ ---------- -------- -------- --------- ----------------------- ----------------------- WKS01 sat_pool_leak CONTOSO\hannah 10 10 1 2026-10-30 12:26:45.397 2026-10-30 12:26:45.457 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
USE [master]; SELECT [s].[session_id] , [s].[status] , [s].[open_transaction_count] , [s].[last_request_end_time] , [c].[connect_time] , [c].[net_transport] FROM [sys].[dm_exec_sessions] AS [s] INNER JOIN [sys].[dm_exec_connections] AS [c] ON [c].[session_id] = [s].[session_id] WHERE [s].[program_name] LIKE N'sat[_]pool[_]%' ORDER BY [s].[last_request_end_time]; |
|
1 2 3 4 5 6 7 8 |
session_id status open_transaction_count last_request_end_time connect_time net_transport ---------- ------ ---------------------- --------------------- ------------ ------------- 93 sleeping 0 2026-10-30 12:26:45.397 2026-10-30 12:26:45.347 Shared memory 97 sleeping 0 2026-10-30 12:26:45.407 2026-10-30 12:26:45.400 Shared memory 98 sleeping 1 2026-10-30 12:26:45.410 2026-10-30 12:26:45.407 Shared memory 99 sleeping 0 2026-10-30 12:26:45.417 2026-10-30 12:26:45.410 Shared memory ... 105 sleeping 0 2026-10-30 12:26:45.457 2026-10-30 12:26:45.450 Shared memory |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
$cs = 'Server=.\SQL2025;Integrated Security=SSPI;Application Name=sat_pool_fixed;Max Pool Size=10;Connect Timeout=5' for ($i = 1; $i -le 1000; $i++) { $conn = New-Object System.Data.SqlClient.SqlConnection $cs try { $conn.Open() $cmd = $conn.CreateCommand() $cmd.CommandText = if ($i -eq 3) { 'BEGIN TRANSACTION; SELECT @@SPID;' } else { 'SELECT @@SPID;' } $spid = $cmd.ExecuteScalar() } finally { $conn.Dispose() } } |
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
- 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. ↩
- 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. ↩
- 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. ↩
- 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. ↩
- 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. ↩