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

by

Troubleshooting Guide: SQL Server Error 867 — Buffer Pool Extension Disabled/Deleted

Why This Error Happens

This message appears when SQL Server 2025 automatically disables and removes the Buffer Pool Extension (BPX) file, typically because the underlying SSD/flash storage path became unavailable, ran out of disk space, or encountered I/O errors during a checkpoint or page read operation. It can also occur if the BPX cache file was manually deleted, moved, or lost permissions while the SQL Server service account no longer has read/write access to it. In some cases, this is a benign informational event triggered intentionally by an administrator running ALTER SERVERCONFIGURATION SET BUFFER POOL EXTENSION OFF, but when unexpected, it usually signals storage instability.

Step-by-Step Resolution Guide

Step 1: Verify the Storage Path and Disk Health

  • Confirm the BPX file path (commonly D:\SQLCache\BPE.BIN) still exists and the drive is online in Disk Management.
  • Check Windows Event Viewer (System log) for Disk or NTFS warnings (Event ID 7, 51, or 153) around the same timestamp.
  • Run chkdsk D: /f (replace D: with your cache drive letter) during a maintenance window to rule out file system corruption.

Step 2: Validate Permissions and Free Space

  • Ensure the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Full Control on the BPX folder via right-click → Properties → Security.
  • Confirm at least 10-20% free space remains on the target SSD; BPX creation fails silently if space is insufficient.
  • Use PowerShell to check free space: Get-PSDrive D | Select-Object Used,Free.

Step 3: Re-enable the Buffer Pool Extension

  • Open SQL Server Management Studio (SSMS) or a query window and run:
  ALTER SERVERCONFIGURATION
  SET BUFFER POOL EXTENSION ON
  (FILENAME = 'D:\SQLCache\BPE.BIN', SIZE = 20GB);
  • Adjust SIZE to 4-8x your physical RAM allocation, not exceeding SSD capacity.
  • Restart the SQL Server service only if the command returns a pending configuration error.

Step 4: Review SQL Server Error Log for Root Trigger

  • Open the log via SSMS → Management → SQL Server Logs, or read directly from C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG.
  • Search for preceding Error 865 or 866 entries, which usually indicate the specific I/O failure that triggered the auto-disable.
  • Correlate timestamps with any storage maintenance, antivirus scans, or SAN failover events.

Alternative or Verification Steps

  • Verify success by querying SELECT * FROM sys.dm_os_buffer_pool_extension_configuration; — the state_description column should read “NORMAL” with is_enabled = 1.
  • If the BPX continues to disable repeatedly, consider relocating the cache file to a different physical SSD or temporarily disabling BPX until underlying storage hardware is replaced or validated.