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

by

Troubleshooting Guide: SQL Server Error 896 – Failed to Disable Buffer Pool Extension

Why This Error Happens

Error 896 typically occurs when SQL Server attempts to disable the Buffer Pool Extension (BPE) while active memory-mapped I/O operations are still referencing the BPE cache file, preventing a clean handoff. This can also happen if the BPE configuration state is corrupted in the server’s internal metadata, or if the service account lacks the necessary file-handle permissions to release the .bpe file on disk. In some cases, a pending transaction or an improperly terminated previous ALTER SERVER CONFIGURATION command leaves the extension in a transitional “disabling” state that blocks subsequent attempts.

Step-by-Step Resolution Guide

Step 1: Verify Current Buffer Pool Extension Status

  • Open SQL Server Management Studio (SSMS) or Azure Data Studio connected to your SQL Server 2025 instance.
  • Run the following query to check BPE state:
SELECT * FROM sys.dm_os_buffer_pool_extension_configuration;
  • Confirm whether state shows 0 (disabled), 1 (enabled), or a transitional value indicating a stuck process.

Step 2: Terminate Blocking Sessions and Retry Disable Command

  • Identify any active sessions holding locks on system resources using:
SELECT * FROM sys.dm_exec_requests WHERE command LIKE '%BUFFER POOL%';
  • Kill any blocking session IDs (SPIDs) found using KILL <session_id>;.
  • Re-attempt the disable command:
ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION OFF;

Step 3: Check File Permissions and Service Account Access

  • Navigate to the directory where the .bpe file resides (commonly C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\).
  • Right-click the .bpe file, go to Properties > Security, and confirm the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Full Control.
  • If permissions are missing, grant Full Control, apply changes, and restart the SQL Server service via services.msc or:
Restart-Service -Name 'MSSQLSERVER' -Force

Step 4: Restart SQL Server Service and Clear Transitional State

  • Fully stop the SQL Server instance using PowerShell:
Stop-Service -Name 'MSSQLSERVER' -Force
  • Manually delete the orphaned .bpe file if it still exists on disk (ensure no process is locking it via Resource Monitor).
  • Restart the service and re-run the ALTER SERVER CONFIGURATION command to disable BPE cleanly.

Alternative or Verification Steps

  • After applying the fix, re-run SELECT * FROM sys.dm_os_buffer_pool_extension_configuration; and confirm the state column returns 0, indicating BPE is fully disabled.
  • If the error persists, as an alternative, use sp_configure 'buffer pool extension', 0; followed by RECONFIGURE; to force the setting through server-level configuration rather than the ALTER SERVER CONFIGURATION syntax.