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

by

Troubleshooting Guide: SQL Server Error 5834 — Affinity Mask Conflict

Why This Error Happens

This error occurs because SQL Server 2025 detects an overlap between the CPUs assigned via affinity mask (or affinity64 mask) and the CPUs assigned via affinity I/O mask. Each CPU core on the host can only be dedicated to one function at a time—either general query processing or dedicated I/O operations—and SQL Server enforces mutual exclusivity between these two pools. This typically happens after a manual edit to sp_configure values or when a server is migrated/reconfigured with a different CPU core count than the original affinity script was written for.

Step-by-Step Resolution Guide

Step 1: Identify the Current Affinity Configuration

  • Open SQL Server Management Studio (SSMS) and connect to the instance via a new query window.
  • Run EXEC sp_configure 'show advanced options', 1; RECONFIGURE; to unlock advanced settings.
  • Run EXEC sp_configure 'affinity mask'; and EXEC sp_configure 'affinity I/O mask'; to view the current bitmask values (or check affinity64 mask / affinity64 I/O mask on 64-bit NUMA systems).
  • Cross-reference these decimal/binary values against Task Manager’s Performance > CPU tab on Windows Server 2025 to map which logical processors are currently assigned to which pool.

Step 2: Recalculate Non-Overlapping CPU Masks

  • Convert both masks to binary and manually check for bit overlap (e.g., if CPU 0–3 are in affinity mask, they cannot also appear in affinity I/O mask).
  • Use the GUI method instead of manual bitmasking: open SSMS > Right-click Server > Properties > Processors tab.
  • In the Processors tab, uncheck any CPU under “Automatically set processor affinity mask for all processors” and manually assign distinct checkboxes for “Processor Affinity” vs. “I/O Affinity” columns, ensuring no single CPU row has both boxes checked.

Step 3: Apply Configuration via T-SQL (Alternative to GUI)

  • If the GUI fails to save due to the same conflict, reset both values to 0 first to clear the conflicting state:
  • Run EXEC sp_configure 'affinity mask', 0; RECONFIGURE;
  • Run EXEC sp_configure 'affinity I/O mask', 0; RECONFIGURE;
  • Re-apply the corrected, non-overlapping decimal values calculated in Step 2 for each setting individually, running RECONFIGURE after each command.

Step 4: Restart the SQL Server Service

  • Open Services.msc on Windows Server 2025.
  • Locate SQL Server (MSSQLSERVER) or the specific named instance.
  • Right-click and select Restart to apply the new kernel-level affinity bindings, as these changes do not take effect dynamically without a service restart in most configurations.

Alternative or Verification Steps

  • Verify the Fix: After the restart, check the SQL Server Error Log (via SSMS > Management > SQL Server Logs) for the startup message confirming “affinity mask” and “I/O affinity mask” values were loaded without a 5834 error.
  • Alternative Fix: If manual calculation is too complex, reset both affinity mask and affinity I/O mask to 0 via T-SQL and leave “Automatically set processor affinity mask for all processors” checked in the GUI; this allows SQL Server to dynamically manage core allocation without manual mask conflicts.