Diagnosing SQL Server Connection Pool Exhaustion with sp_who2

Problem Background

The application repeatedly throws the error “Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool,” indicating that all pooled connections are in use and the maximum pool size has been reached. To identify which program is consuming excessive connections, we need to inspect the active sessions on the SQL Server.

Listing Active Connections

SQL Server provides the system procedures sp_who and sp_who2 to display current sessions. Running the following commands in the master database shows the active connections:

USE master;
GO
EXEC sp_who2;
GO

The result (illustrated in the image below) is a table where each row represents a session. For readability the article shows a trimmed version (Table 1).

Interpreting the Output

Many rows often have Status = sleeping and Command = AWAITING COMMAND . It is important to understand that SQL Server itself does not implement a connection pool; the pool is managed by ADO.NET. When a C# program opens a connection for the first time, SqlClient creates a physical connection to SQL Server. Closing the connection does not immediately close the physical link; instead the connection is returned to the pool and kept alive for a default of 60 seconds. If the same connection string is used again within that interval, SqlClient reuses the pooled connection.

Common Causes of Pool Exhaustion

Failing to close a SqlDataReader after calling ExecuteReader, leaving the connection occupied.

Not providing all required parameters to a stored procedure, causing the query to fail and the connection to remain unreleased.

Using SQL Server Profiler

A practical way to verify whether connections are being reused is to run SQL Server Profiler with the default template. Look for the following events:

Audit Login : indicates a new physical connection has been established.

RPC:Completed with the text sp_reset_connection: indicates a pooled connection has been reset and reused.

If you never see sp_reset_connection, the pool is not being reused and you are likely hitting the exhaustion problem. The profiler screenshot is shown below.

Supplementary Knowledge: sp_who vs sp_who2

sp_who

is officially documented by Microsoft, while sp_who2 is undocumented but widely used because it returns additional columns and presents the data more compactly. Both provide information about current users, sessions, and processes, but sp_who2 includes extra columns such as spid for easier readability.

Postscript

The timeout error often stems from incomplete or failed queries. For example, invoking a stored procedure with missing parameters prevents the query from completing, leaving the connection open. Consequently, ADO.NET cannot return the connection to the pool, leading to continuous creation of new connections until the pool reaches its maximum size.