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

by

Troubleshooting Guide: Error 5853 — Invalid Affinity Range in SQL Server 2025

Why This Error Happens

This error occurs when SQL Server’s ALTER SERVER CONFIGURATION command is executed with a CPU affinity range where the lower-bound CPU ID is numerically equal to or greater than the upper-bound CPU ID. It typically results from a manual scripting mistake, a copy-paste error from a template script, or a dynamic script that incorrectly calculates CPU core boundaries based on a misread cpu_count value from sys.dm_os_sys_info. Additionally, this can surface after a virtual machine migration or a Windows Server 2025 hardware change where the total logical processor count was reduced, leaving old affinity scripts referencing non-existent or reversed CPU ranges.

Step-by-Step Resolution Guide

Step 1: Verify Current CPU Topology

  • Open SQL Server Management Studio (SSMS) and connect to the instance via a Dedicated Administrator Connection (DAC) if the instance is unresponsive.
  • Run the query: SELECT cpu_id, scheduler_id, is_online FROM sys.dm_os_schedulers WHERE status = 'VISIBLE ONLINE'; to confirm the actual number of logical processors available to Windows Server 2025.
  • Cross-reference this with Task Manager’s Performance tab or the command echo %NUMBER_OF_PROCESSORS% in an elevated Command Prompt.

Step 2: Correct the T-SQL Affinity Syntax

  • Locate the script or job step that triggered the error (usually found in SQL Server Agent job history or a deployment script).
  • Ensure the syntax follows the correct low-to-high pattern: ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = 0 TO 7;
  • Do not reverse the values (e.g., CPU = 7 TO 0 is invalid and will trigger Error 5853).
  • If using multiple ranges, ensure commas separate valid ascending pairs, such as CPU = 0 TO 3, 8 TO 11.

Step 3: Reset Affinity via Configuration Manager (If T-SQL is Locked)

  • If the instance fails to start due to a corrupted affinity mask in the registry, open SQL Server Configuration Manager.
  • Right-click the SQL Server service, select Properties, and relaunch with the -f (minimal configuration) startup parameter.
  • Once connected via DAC, run ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = AUTO; to clear the invalid mask entirely.
  • Remove the -f flag and restart the SQL Server service normally.

Step 4: Audit Group Policy and NUMA Settings

  • On Windows Server 2025, verify that no Group Policy Objects (GPOs) are dynamically restricting processor scheduling, which can alter how SQL Server enumerates CPU IDs.
  • Check the Processor Group configuration in the Windows Registry under HKEY_LOCAL_MACHINE\HARDWARE\DESCRIPTION\System\CentralProcessor to ensure the OS exposes the expected core count to the SQL Server process.

Alternative or Verification Steps

  • Verify the fix by re-running the ALTER SERVER CONFIGURATION command with the corrected ascending range and checking the SQL Server Error Log for the confirmation message: “A new process affinity was set.”
  • If the error persists after setting CPU = AUTO, consider rebuilding the master database’s configuration via the -T4022 trace flag to bypass custom startup procedures that might be re-triggering the faulty affinity script.