Snippet Name: Port_Exhaustion_in_SQL
Tags:
Created Date: 2025-06-16 13:12:42
Last Modified Date: 2025-05-31 10:32:04
Dependencies:
DBA Notes:
Sample Output:
Title: Port Exhaustion in SQL
Author: user
in SQL Server 2019, i had an issue where although there was only roughly 1000 connections, noone could connect, or existing connections would time out and disconnect, evenr from SMS. someone mentioned "port Exhausttion" do you have a query to measure or display that
Author: ChatGPT
Yes, "port exhaustion" can occur when the number of available ephemeral ports is exhausted, preventing new connections from being established. In SQL Server, this can lead to connection timeouts or dropped sessions.
### **Checking for Port Exhaustion in SQL Server 2019**
Since SQL Server relies on TCP/IP connections, you can check the number of active connections and the ports being used by querying the **Dynamic Management Views (DMVs)**.
#### **1. Check Active Connections Per IP and Port**
This query will show the number of connections grouped by IP and port:
```sql
SELECT
c.client_net_address,
e.local_tcp_port,
COUNT(*) AS connection_count
FROM sys.dm_exec_connections e
JOIN sys.dm_exec_sessions s ON e.session_id = s.session_id
JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
JOIN sys.dm_exec_connections c ON e.session_id = c.session_id
GROUP BY c.client_net_address, e.local_tcp_port
ORDER BY connection_count DESC;
```
#### **2. Check Total Active TCP Connections**
To check the number of active TCP connections (which may indicate port exhaustion), run:
```sql
SELECT
protocol_desc,
COUNT(*) AS active_connections
FROM sys.dm_exec_connections
WHERE protocol_desc = 'TCP'
GROUP BY protocol_desc;
```
#### **3. Checking TCP Ports at OS Level**
Since SQL Server itself does not track the ephemeral port usage directly, you need to check at the OS level using **PowerShell** or **netstat**.
##### **Using PowerShell**
This command will show the number of TCP connections per port:
```powershell
Get-NetTCPConnection | Group-Object -Property LocalPort | Sort-Object Count -Descending
```
##### **Using netstat**
Run this command in **Command Prompt**:
```cmd
netstat -ano | findstr /C:"TIME_WAIT" /C:"SYN_SENT" /C:"ESTABLISHED"
```
If you see **thousands of entries** in the `TIME_WAIT` state, it suggests that ephemeral ports are exhausted.
### **4. Check Ephemeral Port Range and Usage**
You can check the number of available ephemeral ports using:
```powershell
netsh int ipv4 show dynamicport tcp
```
This will show the **starting and ending port range** along with the total available ephemeral ports.
### **5. Monitoring SQL Server Network Utilization**
If you suspect excessive connections are causing port exhaustion, use:
```sql
SELECT
COUNT(*) AS total_connections,
SUM(CASE WHEN status = 'sleeping' THEN 1 ELSE 0 END) AS sleeping_connections,
SUM(CASE WHEN status = 'running' THEN 1 ELSE 0 END) AS active_connections
FROM sys.dm_exec_sessions;
```
### **Fixes for Port Exhaustion**
If port exhaustion is occurring:
1. **Increase the Ephemeral Port Range** (Run as Admin in CMD):
```cmd
netsh int ipv4 set dynamicport tcp start=10000 num=55535
```
This increases the available ports.
2. **Reduce TIME_WAIT Delay** (for shorter connection re-use):
```cmd
netsh int tcp set global MaxUserPort=65534
netsh int tcp set global TcpTimedWaitDelay=30
```
3. **Check for excessive sleeping connections** that are consuming ports and try to optimize application behavior.
Would you like help automating a real-time port monitoring query for SQL Server?
Author: user
would port exhaustion possibly be related to an applciaiton not closing previosu connecitons
Author: ChatGPT
Yes, **port exhaustion** can definitely be related to an application not properly closing previous connections. Here???s how:
### **How an Application Can Cause Port Exhaustion**
1. **Connection Leaks** ??? If an application repeatedly opens new connections without closing them, it will gradually consume all available ephemeral ports.
2. **Long-Lived `TIME_WAIT` States** ??? If connections are closed improperly or too frequently, they may linger in the `TIME_WAIT` state, causing ports to remain unavailable.
3. **Excessive Idle or Sleeping Sessions** ??? If an application does not efficiently manage database connections, connections may stay open and accumulate.
4. **Misconfigured Connection Pooling** ??? Applications using connection pooling should properly reuse and close connections rather than opening new ones unnecessarily.
---
### **How to Check for Orphaned or Idle Connections**
Run the following SQL query to check for **sleeping connections** (potentially unused but still open):
```sql
SELECT
COUNT(*) AS total_connections,
SUM(CASE WHEN status = 'sleeping' THEN 1 ELSE 0 END) AS sleeping_connections,
SUM(CASE WHEN status = 'running' THEN 1 ELSE 0 END) AS active_connections,
MAX(login_time) AS oldest_connection_time
FROM sys.dm_exec_sessions
WHERE is_user_process = 1;
```
If there are an excessive number of **sleeping** connections, it could indicate that the application is not closing them properly.
---
### **Checking TCP Connection States in Windows (Identifying TIME_WAIT Issues)**
If you suspect a large number of open ports, you can check **TCP connection states** using **PowerShell**:
```powershell
Get-NetTCPConnection | Group-Object -Property State | Sort-Object Count -Descending
```
If `TIME_WAIT` has **thousands of entries**, it means that old connections are lingering too long.
Alternatively, using **netstat**:
```cmd
netstat -ano | findstr "TIME_WAIT"
```
This will show all connections stuck in the `TIME_WAIT` state.
---
### **How to Fix the Issue**
1. **Fix the Application Code**
- Ensure the application **explicitly closes database connections** when they are no longer needed.
- If using **connection pooling**, set a reasonable **idle timeout**.
2. **Adjust SQL Server's Remote Query Timeout**
If queries take too long and hold connections open, you can reduce the remote query timeout:
```sql
EXEC sp_configure 'remote query timeout', 60; -- Default is 600 seconds
RECONFIGURE;
```
3. **Increase Available Ephemeral Ports**
If port exhaustion occurs due to a large number of concurrent connections, **increase the range of ephemeral ports**:
```cmd
netsh int ipv4 set dynamicport tcp start=10000 num=55535
```
4. **Reduce TIME_WAIT Timeout**
Windows keeps closed connections in `TIME_WAIT` for **240 seconds** by default. You can reduce this:
```cmd
netsh int tcp set global TcpTimedWaitDelay=30
```
5. **Enable TCP Keepalive in SQL Server**
If connections are being left open indefinitely, SQL Server's **TCP Keepalive** feature can help close idle connections:
```sql
EXEC sp_configure 'remote login timeout', 10; -- Reduce timeout for stale connections
RECONFIGURE;
```
Would you like help identifying the exact application causing the issue? You can use **Extended Events** or **sys.dm_exec_requests** to trace active sessions from the problem application.