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

by

Troubleshooting Guide: SQL Server Error 3870 – TempDB Metadata Memory-Optimized Configuration Change Pending

Why This Error Happens

This message appears when an administrator enables or disables the tempdb metadata memory-optimized feature using ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA, but the change requires a full SQL Server service restart to take effect. The setting is stored in a pending state within the server configuration metadata and SQL Server cannot dynamically re-initialize the tempdb system catalogs or memory-optimized filegroup structures while the instance is online. Until a restart occurs, SQL Server continues operating under the old tempdb configuration, which can cause confusion if administrators expect the change to be immediate.

Step-by-Step Resolution Guide

Step 1: Confirm the Pending Configuration Change

  • Run the following query to check the current and pending state:
  • SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized');
  • Also check sys.configurations or the error log for confirmation that the change was staged correctly.
  • Ensure no typos or incorrect values were passed in the original ALTER SERVER CONFIGURATION statement.

Step 2: Schedule and Perform a SQL Server Service Restart

  • On Windows Server 2025 / Windows 11, open SQL Server Configuration Manager.
  • Navigate to SQL Server Services, right-click your instance (e.g., SQL Server (MSSQLSERVER)), and select Restart.
  • Alternatively, use PowerShell with elevated privileges:
  • Restart-Service -Name 'MSSQLSERVER' -Force
  • If using a named instance, replace MSSQLSERVER with MSSQL$<InstanceName>.

Step 3: Validate TempDB Initialization Post-Restart

  • After the service restarts, confirm SQL Server successfully initializes tempdb metadata in memory-optimized mode.
  • Check the SQL Server Error Log (via SSMS or C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for confirmation messages indicating tempdb started correctly.
  • Look for any additional errors (e.g., insufficient memory grant pool or XTP filegroup misconfiguration) that may block memory-optimized tempdb activation.

Step 4: Re-Verify Server Configuration Setting

  • Re-run the SERVERPROPERTY('IsTempdbMetadataMemoryOptimized') query to ensure the value now reflects 1 (enabled) or 0 (disabled) as intended.
  • If the setting still shows a pending state, confirm there isn’t a cluster failover or Always On Availability Group delaying propagation on all replicas.

Alternative or Verification Steps

  • Verify success by querying sys.dm_db_file_space_usage and sys.database_files to ensure tempdb memory-optimized metadata objects are correctly created and accessible.
  • If the restart doesn’t resolve the issue, check for conflicting Group Policy restart delays or pending Windows Updates on Windows Server 2025 that may prevent the SQL Server service from fully reinitializing.