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

by

Why This Error Happens

Error 5856 occurs when a CPU affinity range or soft-NUMA configuration stored in SQL Server’s configuration (via ALTER SERVER CONFIGURATION SET PROCESS AFFINITY or registry-based affinity masks) references a CPU ID that no longer exists on the host. This commonly happens after a virtual machine or physical server has its vCPU/core count reduced, or when a configuration script written for a higher-core-count system is applied to a smaller instance. SQL Server validates the affinity range at startup against the actual logical processor count exposed by Windows Server 2025 / Windows 11, and any CPU ID above that maximum triggers this error.

Step-by-Step Resolution Guide

Step 1: Confirm the Actual Logical Processor Count

  • Open PowerShell as Administrator and run Get-WmiObject Win32_Processor | Select NumberOfLogicalProcessors.
  • Alternatively, check Task Manager > Performance > CPU > Logical Processors.
  • Note this number — it represents the maximum CPU ID SQL Server can reference (CPU IDs are zero-indexed).

Step 2: Inspect the Current SQL Server Affinity Configuration

  • Connect via SQLCMD or SSMS and run:
    SELECT cpu_id, scheduler_id, is_online FROM sys.dm_os_schedulers WHERE status = 'VISIBLE ONLINE';
  • Also check sys.configurations for affinity mask and affinity64 mask values, or run:
    SELECT * FROM sys.dm_os_sys_info;
  • Identify whether the configured range (e.g., CPU 0–31) exceeds the actual available CPUs (e.g., only 16 present).

Step 3: Reset or Correct the Affinity Configuration

  • If using the modern syntax, reset affinity to AUTO:
    ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = AUTO;
  • If you need a specific range, scope it correctly, e.g.:
    ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = 0 TO 15; (adjust upper bound to match Step 1’s result).
  • If affinity was set via sp_configure 'affinity mask', run:
    EXEC sp_configure 'affinity mask', 0; RECONFIGURE;

Step 4: Clear Stale Soft-NUMA Registry Entries (If Applicable)

  • Open Registry Editor and navigate to:
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL16.<InstanceName>\MSSQLServer\NodeConfiguration
  • Remove or correct any CPUMask values referencing CPU IDs beyond the current logical processor count.
  • Restart the SQL Server service for changes to take effect.

Step 5: Restart the SQL Server Service

  • Restart via services.msc or run in PowerShell:
    Restart-Service -Name 'MSSQLSERVER' (or the named instance service).
  • Confirm the SQL Server Error Log no longer logs Error 5856 on startup by checking:
    EXEC xp_readerrorlog 0, 1, N'5856';

Alternative or Verification Steps

  • Verify the fix by querying sys.dm_os_schedulers again and confirming all cpu_id values are marked is_online = 1 with no gaps or out-of-range entries.
  • If the error persists after resetting affinity, check for third-party monitoring tools or Resource Governor pools that may also hardcode CPU/NUMA ranges, and reset those configurations as well.