How to fix Error 5828 on Windows Server 2025 / Windows 11

by

Troubleshooting Guide: SQL Server Error 5828 — “User Connections Are Limited to %d”

Why This Error Happens

This error occurs when SQL Server 2025 reaches the configured ceiling for the user connections setting, which caps the maximum number of simultaneous user connections the instance will accept. It can happen because an administrator manually set a low, fixed value in sp_configure, or because the system calculated a default limit based on insufficient memory or stale configuration metadata left over from a migration. In some cases, orphaned connections from application pools or linked server sessions that are not properly closed can also exhaust the available connection slots faster than expected.

Step-by-Step Resolution Guide

Step 1: Check the Current User Connections Configuration

  • Open SQL Server Management Studio (SSMS) or sqlcmd and run:
  • EXEC sp_configure 'user connections';
  • Review the run_value column — a value of 0 means “unlimited” (dynamically allocated), while any positive number is a hard cap.
  • Note the current limit reported in the Error 5828 message to compare against this output.

Step 2: Reset or Increase the User Connections Limit

  • To allow SQL Server to manage connections dynamically, run:
  • EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
  • EXEC sp_configure 'user connections', 0;
  • RECONFIGURE;
  • If a specific higher limit is required instead of 0, replace it with an appropriate integer (e.g., 500), based on expected concurrent workloads.
  • Restart the SQL Server service via services.msc or Restart-Service MSSQLSERVER in PowerShell (Windows Server 2025/Windows 11) for the change to take effect.

Step 3: Identify and Terminate Orphaned Connections

  • Run sp_who2 or query sys.dm_exec_sessions to identify idle or orphaned sessions consuming connection slots.
  • SELECT session_id, login_name, status, last_request_end_time FROM sys.dm_exec_sessions WHERE is_user_process = 1;
  • Use KILL <session_id>; to manually terminate stale or zombie sessions if the application layer is not releasing them properly.

Step 4: Audit Application Connection Pooling Settings

  • Check connection strings in IIS, .NET apps, or middleware for Max Pool Size settings that may be set unnecessarily high.
  • Ensure connection pooling is enabled (Pooling=True) to reuse connections efficiently instead of opening new ones per request.
  • Adjust Min Pool Size and Max Pool Size values to align with the SQL Server’s actual user connections limit.

Alternative or Verification Steps

  • Verify the fix by monitoring active connections in real time using SELECT COUNT(*) FROM sys.dm_exec_connections; and confirming it stays below the configured threshold during peak load.
  • If Error 5828 persists after reconfiguration, check Windows Server 2025 Event Viewer (Applications and Services Logs > MSSQLSERVER) for memory pressure warnings, as insufficient RAM can also cause SQL Server to silently cap connections regardless of the sp_configure value.