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

by

Troubleshooting Guide: SQL Server Error 3878 – Tiered Memory Memory-Optimized Configuration Change Requires Restart

Why This Error Happens

This informational error occurs because SQL Server 2025’s tiered memory feature for memory-optimized tables requires a server-level restart to apply changes to how memory-optimized data is distributed between DRAM and persistent memory (PMem) or secondary tiers. The configuration change is written to the registry or configuration metadata immediately, but the Storage Engine’s memory-optimized filegroup bindings are only re-initialized during SQL Server startup. Until the restart occurs, SQL Server continues operating under the previous tiered memory setting, which can cause confusion if administrators expect the change to be live immediately.

Step-by-Step Resolution Guide

Step 1: Confirm the Configuration Change Was Applied

  • Run EXEC sp_configure 'tiered memory optimization'; in SQL Server Management Studio (SSMS) to verify the new value is staged.
  • Check the SQL Server Error Log for the exact text of Error 3878 confirming the setting that was captured (%S_MSG will show either “enabled” or “disabled”).
  • Note the timestamp to ensure no conflicting configuration changes were made afterward.

Step 2: Schedule and Perform a Controlled Restart

  • On Windows Server 2025/Windows 11, open Services.msc and locate SQL Server (MSSQLSERVER) or the named instance.
  • Right-click and select Restart, or use PowerShell: Restart-Service -Name 'MSSQLSERVER' -Force.
  • Ensure any active transactions or Always On Availability Group failovers are accounted for before restarting in production.

Step 3: Validate Tiered Memory and PMem/Storage Tier Binding

  • After restart, confirm the physical storage path for the tiered memory-optimized filegroup is accessible (check sys.database_files and the PMem-aware NTFS/DAX volume mount points).
  • Verify Windows Server 2025 has the storage class memory driver (SCMBus/PMem driver) loaded correctly via Device Manager.
  • Confirm the SQL Server service account has read/write NTFS permissions on the tiered storage mount path (e.g., E:\SQLData\TieredMem).

Step 4: Re-check Server-Level Configuration Post-Restart

  • Run EXEC sp_configure 'tiered memory optimization'; again and confirm the run_value now matches the config_value.
  • Review the SQL Server Error Log to confirm no Error 3878 messages reappear on subsequent startups, indicating the setting is now stable.

Alternative or Verification Steps

  • Run SELECT * FROM sys.dm_db_xtp_memory_consumers; to confirm memory-optimized objects are now using the expected tiered memory allocation.
  • If the setting reverts after restart, verify no other process (such as a secondary DBA script, Group Policy, or Always On synchronization) is overwriting sp_configure values, and consider applying the change via a Policy-Based Management script to enforce consistency across replicas.