Snippet Name: Detecting_Port_Exhaustion_SQL
Tags:
Created Date: 2025-06-16 13:12:39
Last Modified Date: 2025-05-31 10:32:06
Dependencies:
DBA Notes:
Sample Output:
Title: Detecting Port Exhaustion SQL
Author: user
how to detect port exhaustion in windows for sql server
Author: ChatGPT
Port exhaustion occurs when all available ephemeral ports are used up, preventing new outbound connections from being established. In SQL Server, this can lead to connection failures, timeouts, or errors related to TCP/IP connectivity.
### **Steps to Detect Port Exhaustion in Windows for SQL Server**
#### **1. Check for Errors in SQL Server Logs**
- Open SQL Server error logs and look for messages indicating connection failures.
- Errors like `10048 (WSAEADDRINUSE)` suggest port exhaustion.
```sql
EXEC xp_readerrorlog 0, 1, '10048';
```
#### **2. Check Available Ports Using `netstat`**
Run the following command to check the number of ports in use:
```powershell
netstat -ano | findstr :1433 | findstr ESTABLISHED | find /C /V ""
```
This counts the number of established connections to SQL Server's default port (1433). If this number is close to the ephemeral port limit, you might be experiencing exhaustion.
To check all ephemeral ports:
```powershell
netstat -ano | findstr : | find /C /V ""
```
#### **3. Check Ephemeral Port Range**
The ephemeral port range determines how many ports are available for outbound connections. To check the range:
```powershell
netsh int ipv4 show dynamicport tcp
```
Typical output:
```
Protocol tcp Dynamic Port Range
---------------------------------
Start Port : 49152
Number of Ports : 16384
```
If your system is frequently running out of ports, you may need to increase this range.
#### **4. Check TCP/IP Connection Limits**
Run this command to check current active connections:
```powershell
netstat -q
```
Look for the **"bound but not established"** count???if this number is very high, it suggests possible port exhaustion.
#### **5. Monitor Port Usage with Performance Monitor**
1. Open **Performance Monitor (`perfmon.msc`)**.
2. Add a counter:
- **Category**: TCPv4
- **Counter**: Connections Established
- **Counter**: Connection Failures
3. Monitor for unusual spikes.
#### **6. Use PowerShell to Count Used Ports**
Run the following PowerShell command to count how many ephemeral ports are in use:
```powershell
Get-NetTCPConnection | Where-Object { $_.LocalPort -ge 49152 -and $_.LocalPort -le 65535 } | Measure-Object
```
If this number is close to the available range, you are experiencing port exhaustion.
#### **7. Identify Process Consuming Most Ports**
Run:
```powershell
Get-NetTCPConnection | Group-Object -Property OwningProcess | Sort-Object Count -Descending | Format-Table Count, Name
```
Find the Process ID (PID) consuming the most ports and identify it with:
```powershell
Get-Process -Id <PID>
```
If SQL Server or a related process (e.g., a monitoring tool, SSIS, or SSRS) is consuming too many ports, that may be the cause.
---
### **Preventive Measures**
1. **Increase the Ephemeral Port Range** (if necessary):
```powershell
netsh int ipv4 set dynamicport tcp start=49152 num=32768
```
Then restart the system.
2. **Close Idle Connections**:
- Reduce **Remote Query Timeout** in SQL Server.
- Enable **Connection Pooling** to reuse ports efficiently.
3. **Use a Fixed Port for SQL Server**:
- Instead of dynamic ports, configure SQL Server to use a static port (e.g., 1433).
4. **Check for Excessive Client Connections**:
- If applications keep many idle connections open, consider using connection pooling.
Would you like help automating the detection with a script?
Author: user
appears to be non-yielding on Scheduler 38
Author: ChatGPT
The error message **"appears to be non-yielding on Scheduler 38"** in SQL Server typically indicates that a worker thread running on **Scheduler 38** has been stuck for an extended period without yielding execution back to the SQL Server scheduler. This can cause performance degradation, long query execution times, and even server hangs.
### **How to Investigate and Resolve the Issue**
#### **1. Check SQL Server Error Log for Additional Details**
Run the following query to look for detailed error messages related to non-yielding schedulers:
```sql
EXEC xp_readerrorlog 0, 1, 'non-yielding';
```
This should return logs related to any **non-yielding schedulers**, including possible stack dumps.
#### **2. Identify Blocked or Long-Running Queries**
If a query is stuck and causing the scheduler to become non-yielding, use the following query to find **long-running sessions**:
```sql
SELECT session_id, status, blocking_session_id, wait_type, wait_time, last_wait_type, wait_resource, cpu_time, total_elapsed_time, program_name, text
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE total_elapsed_time > 5000; -- Queries running longer than 5 seconds
```
If a query has **high CPU time but low elapsed time**, it might be consuming excessive CPU on **Scheduler 38**.
#### **3. Check for Blocked or Stuck Threads**
SQL Server maintains a list of tasks assigned to each scheduler. You can check the **current workload on Scheduler 38** using:
```sql
SELECT scheduler_id, current_workers_count, active_workers_count, work_queue_count, runnable_tasks_count, is_online
FROM sys.dm_os_schedulers
WHERE scheduler_id = 38;
```
- **`runnable_tasks_count` > 0** means tasks are waiting to execute.
- **`work_queue_count` > 0** suggests work is piling up.
- **`is_online = 0`** means the scheduler is offline (which is rare).
#### **4. Look for High CPU Usage or Spinlocks**
Check CPU-intensive queries using:
```sql
SELECT r.session_id, r.status, r.cpu_time, r.total_elapsed_time, r.wait_type, t.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle)
ORDER BY r.cpu_time DESC;
```
For **Spinlocks**, which indicate excessive contention in SQL Server:
```sql
SELECT * FROM sys.dm_os_spinlock_stats
WHERE collisions > 1000
ORDER BY spins DESC;
```
#### **5. Identify System Waits**
Check wait types that could indicate **CPU or disk pressure**:
```sql
SELECT wait_type, wait_time_ms, signal_wait_time_ms, waiting_tasks_count
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC;
```
Common **non-yielding** scheduler wait types include:
- **SOS_SCHEDULER_YIELD** ??? High CPU contention.
- **CXPACKET** ??? Parallelism waits.
- **PAGELATCH_UP** or **WRITELOG** ??? I/O bottlenecks.
#### **6. Check System-Level CPU Usage**
Open **Task Manager** or use **Performance Monitor (`perfmon`)**:
- Look for **SQL Server using high CPU**.
- Check **disk I/O bottlenecks**.
Use **PowerShell** to monitor CPU:
```powershell
Get-Process sqlservr | Select-Object ProcessName, CPU, Threads
```
#### **7. Check for Recent Changes**
- **Did you apply a patch/update?** Some cumulative updates (CUs) introduce new bugs.
- **Did you change server configuration settings?** Example: `MAXDOP`, `Cost Threshold for Parallelism`, or `Affinity Masking`.
- **Are there new workloads or queries running?**
#### **8. Collect SQL Server Dumps for Further Analysis**
If the issue is persistent and causing severe performance issues, generate a memory dump:
```sql
DBCC STACKDUMP;
```
You can analyze the dump with **WinDbg** or send it to Microsoft Support.
---
### **Possible Fixes**
1. **If the issue is due to high CPU usage**:
- Identify and optimize CPU-intensive queries.
- Adjust **MAXDOP** and **Cost Threshold for Parallelism**.
- Consider enabling **Query Store** to analyze regression.
2. **If there are excessive waits (I/O contention)**:
- Optimize indexes and reduce query reads.
- Ensure TempDB has enough files (match to CPU cores).
- Check disk latency with **sys.dm_io_virtual_file_stats**.
3. **If the issue is caused by blocking or deadlocks**:
- Check blocking chains using:
```sql
EXEC sp_whoisactive;
```
- Identify deadlocks using:
```sql
SELECT * FROM sys.dm_tran_locks WHERE request_status = 'WAIT';
```
- Consider lowering the **transaction isolation level**.
4. **If a single thread is stuck (scheduler starvation)**:
- Try **KILLing the session**:
```sql
KILL <session_id>;
```
- If SQL Server is unresponsive, restart the instance.
---
### **Final Steps**
1. **Monitor SQL Server for Recurrence**:
- If the issue happens frequently, set up an **Extended Event session** to capture long-running queries.
- Monitor SQL Server's **Schedulers** periodically using:
```sql
SELECT * FROM sys.dm_os_schedulers WHERE scheduler_id = 38;
```
2. **Apply SQL Server Updates**:
- Check if a **Cumulative Update (CU) or Service Pack** addresses the issue.
3. **If the problem persists**:
- Contact **Microsoft Support** with collected dump files.
Would you like me to automate scheduler monitoring with a script?
Author: user
Error: 14420, Severity: 16, State: 1.
Author: ChatGPT
### **SQL Server Error 14420: "The log shipping secondary database has not received a backup log file"**
#### **Meaning of the Error**
Error **14420** occurs in **SQL Server Log Shipping** when the secondary database has not received a transaction log backup within the expected threshold.
#### **Possible Causes**
1. **Log Backup Job is Not Running** (on the primary server).
2. **Network Connectivity Issues** preventing the secondary server from receiving log backups.
3. **SQL Agent is Stopped** on the primary or secondary server.
4. **Log Shipping Configuration Issues**, such as incorrect folder permissions or missing transaction log files.
5. **Disk Space Issues** preventing the transaction log files from being created or copied.
6. **Latency in Log Shipping Jobs** causing backups to be created but not copied or restored in time.
7. **Primary Database Recovery Model Changed** (should be **FULL** or **BULK-LOGGED** for log shipping).
8. **SQL Server Restart or Failover Events** disrupting the log shipping process.
---
### **Steps to Troubleshoot and Resolve**
#### **1. Check the Status of Log Shipping Jobs**
Run the following query on the **secondary server** to check log shipping status:
```sql
EXEC master.dbo.sp_help_log_shipping_monitor;
```
- If `last_copied_file` is outdated, there is an issue with the **copy job**.
- If `last_restored_file` is outdated, there is an issue with the **restore job**.
#### **2. Check SQL Server Error Logs on the Primary and Secondary Server**
Run the following command on both servers:
```sql
EXEC xp_readerrorlog 0, 1, '14420';
```
Look for details related to missing transaction log backups.
#### **3. Verify the Log Shipping Jobs**
On the **primary server**, run:
```sql
SELECT * FROM msdb.dbo.log_shipping_primary_databases;
```
On the **secondary server**, run:
```sql
SELECT * FROM msdb.dbo.log_shipping_secondary_databases;
```
Check if `last_backup_file` and `last_restored_file` are current.
#### **4. Check Transaction Log Backups**
Run the following query on the **primary server** to check if transaction log backups are happening:
```sql
SELECT TOP 10 backup_finish_date, type, database_name, physical_device_name
FROM msdb.dbo.backupset b
JOIN msdb.dbo.backupmediafamily f ON b.media_set_id = f.media_set_id
WHERE b.database_name = 'YourDatabase'
AND b.type = 'L'
ORDER BY backup_finish_date DESC;
```
- If **no recent backups** exist, the **log backup job is failing**.
- If backups exist but are **not being copied**, check the **copy job**.
#### **5. Manually Copy and Restore the Last Log Backup**
- Navigate to the backup folder on the **primary server**.
- Copy the latest `.trn` file manually to the **secondary server's restore folder**.
- Restore the log manually:
```sql
RESTORE LOG YourDatabase
FROM DISK = 'C:\Path\To\LogBackup.trn'
WITH STANDBY = 'C:\Path\To\UNDOFile.tuf';
```
If this works, the **log shipping copy job** is failing.
#### **6. Check SQL Server Agent Jobs**
Run the following to check if the **log backup job is running**:
```sql
EXEC msdb.dbo.sp_help_job @job_name = 'LSBackup_YourDatabase';
```
On the **secondary server**, check the **copy and restore jobs**:
```sql
EXEC msdb.dbo.sp_help_job @job_name = 'LSCopy_YourDatabase';
EXEC msdb.dbo.sp_help_job @job_name = 'LSRestore_YourDatabase';
```
If the job is **disabled or failing**, enable and restart it.
#### **7. Verify Log Shipping Agent Job History**
Run the following on the **secondary server**:
```sql
SELECT * FROM msdb.dbo.log_shipping_monitor_history_detail
ORDER BY log_time DESC;
```
Look for recent failures.
#### **8. Check Disk Space on Primary and Secondary Servers**
Ensure there is enough space in the **backup folder** and **log shipping folder**.
```powershell
Get-PSDrive | Where-Object { $_.Free -lt 10GB }
```
If the disk is full, move or delete old backup files.
#### **9. Increase Log Shipping Threshold**
If logs are delayed but arriving, increase the alert threshold:
```sql
EXEC master.dbo.sp_change_log_shipping_primary_database
@database = 'YourDatabase',
@threshold_alert_enabled = 1,
@threshold_alert_threshold = 60; -- Increase threshold in minutes
```
#### **10. Restart SQL Server Agent**
If the jobs are not running, restart SQL Server Agent:
```powershell
Restart-Service SQLSERVERAGENT -Force
```
---
### **Final Steps**
1. **Ensure SQL Server Agent is running** on both servers.
2. **Manually trigger the backup, copy, and restore jobs** to verify they work.
3. **Monitor for future occurrences** by setting up alerts:
```sql
EXEC msdb.dbo.sp_add_alert
@name = 'Log Shipping Alert',
@message_id = 14420,
@severity = 16,
@enabled = 1,
@delay_between_responses = 900, -- 15 minutes
@include_event_description_in = 1,
@category_name = 'Database';
```
---
### **If the Issue Persists**
- **Check for network issues** (`ping`, `tracert`, `telnet <secondary_server> 1433`).
- **Run an Extended Event session** to capture log shipping errors.
- **Reconfigure Log Shipping** if necessary.
Would you like a **PowerShell script to automate log shipping status checks?**