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

by

Troubleshooting Guide: SQL Server Error 5842 — “Too Few Worker Threads Are Specified”

Why This Error Happens

This error occurs when the max worker threads configuration value in SQL Server is manually set below the minimum threshold required by the engine to handle scheduler, CPU, and network configuration demands. SQL Server 2025 calculates a dynamic minimum based on the number of logical processors and active network protocols, and any value entered below this calculated floor will cause the instance to reject the setting at startup or during reconfiguration. This typically results from a DBA manually editing sp_configure values, a flawed automated deployment script, or a corrupted configuration value inherited during an in-place upgrade to SQL Server 2025 on Windows Server 2025.

Step-by-Step Resolution Guide

Step 1: Verify the Current Configuration Value

  • Open SQL Server Management Studio (SSMS) or sqlcmd and connect to the instance (if it allows connection) or prepare to use the Dedicated Administrator Connection (DAC) if the service fails to start.
  • Run the following query to check current settings:
  EXEC sp_configure 'show advanced options', 1;
  RECONFIGURE;
  EXEC sp_configure 'max worker threads';
  • Note the config_value and run_value columns; a config_value of anything other than 0 (auto-configure) that is too low is the likely culprit.

Step 2: Reset Max Worker Threads to Auto-Configure (0)

  • If you can connect to the instance, run:
  EXEC sp_configure 'show advanced options', 1;
  RECONFIGURE;
  EXEC sp_configure 'max worker threads', 0;
  RECONFIGURE;
  • Setting this value to 0 allows SQL Server 2025 to dynamically calculate the optimal thread count based on your CPU core count and network configuration, which is the Microsoft-recommended default for most workloads.
  • Restart the SQL Server (MSSQLSERVER) service via the Services console (services.msc) or via PowerShell: Restart-Service -Name MSSQLSERVER -Force.

Step 3: Start SQL Server with a Minimal Configuration (If Service Won’t Start)

  • If the service fails to start due to this error, open Command Prompt as Administrator and start SQL Server with the -f (minimal configuration) startup parameter:
  NET START MSSQLSERVER /f
  • Alternatively, add -f as a startup parameter in SQL Server Configuration Manager under SQL Server Services > Properties > Startup Parameters, then restart the service.
  • Once connected under minimal configuration, repeat Step 2 to reset the max worker threads value to 0, then remove the -f flag from Startup Parameters and restart the service normally.

Step 4: Check for Registry or Script-Based Overrides

  • Inspect any deployment automation (DSC configs, Ansible playbooks, or T-SQL deployment scripts) that may be setting max worker threads as part of post-install configuration.
  • Correct the hardcoded value in the script to either 0 or a value calculated using Microsoft’s formula: minimum threads = 512 (for 64-bit) plus additional threads based on processor count, to prevent recurrence on the next deployment.

Alternative or Verification Steps

  • After restarting the service, confirm the fix by re-running EXEC sp_configure 'max worker threads'; and checking the SQL Server Error Log (via EXEC sp_readerrorlog; or Event Viewer under Windows Logs > Application) to ensure Error 5842 no longer appears during startup.
  • If the issue persists even after setting the value to 0, verify there isn’t a conflicting Resource Governor pool configuration or an outdated registry entry under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<InstanceID>\MSSQLServer that is overriding the sp_configure value.