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

by

Troubleshooting Guide: SQL Server Error 5217 — Empty Non-Leaf Level Index Page

Why This Error Happens

Error 5217 occurs when SQL Server’s online index rebuild or DBCC shrink/reorganize operation encounters an index B-tree page at a non-leaf level that contains no valid row entries, which violates expected index structure integrity. This typically stems from underlying index corruption caused by storage-level faults, improper shutdowns, or disk I/O errors that left the index metadata in an inconsistent state. It can also surface when a database file shrink operation attempts to relocate pages within a logically damaged index tree on Windows Server 2025 or Windows 11 hosted instances.

Step-by-Step Resolution Guide

Step 1: Confirm the Scope of Corruption

  • Run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; in SQL Server Management Studio (SSMS) to identify all affected objects.
  • Note the object ID and index ID reported alongside Error 5217 for targeted repair.
  • Check the SQL Server error log (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for correlating I/O or storage warnings.

Step 2: Rebuild the Affected Index

  • Identify the specific index using sys.indexes and sys.objects joined on the object ID from the DBCC output.
  • Run a full offline rebuild: ALTER INDEX [IndexName] ON [Schema].[TableName] REBUILD WITH (ONLINE = OFF, MAXDOP = 1);
  • Avoid ONLINE = ON rebuilds initially, as the empty non-leaf page issue often reoccurs during online page-movement operations.

Step 3: Repair Database-Level Corruption if Rebuild Fails

  • Put the database in single-user mode: ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
  • Run DBCC CHECKDB ('YourDatabaseName', REPAIR_REBUILD); first, escalating to REPAIR_ALLOW_DATA_LOSS only if REPAIR_REBUILD does not resolve the page structure issue.
  • Return the database to multi-user mode: ALTER DATABASE YourDatabaseName SET MULTI_USER;

Step 4: Validate Underlying Storage Health

  • Check Windows Server 2025 Event Viewer under Windows Logs > System for Disk or NTFS warning events (Event ID 7, 51, or 153) that may indicate failing storage.
  • Run chkdsk C: /f /r (adjust drive letter to your SQL data volume) during a maintenance window to rule out physical disk corruption.
  • Verify antivirus or backup software is not locking .mdf/.ndf files during active I/O operations.

Step 5: Restore From Backup if Repair Is Unsuccessful

  • If corruption persists after repair attempts, restore the database from the most recent clean full backup plus transaction log backups.
  • Use RESTORE DATABASE YourDatabaseName FROM DISK = 'D:\Backups\YourDatabaseName.bak' WITH REPLACE, RECOVERY;

Alternative or Verification Steps

  • Re-run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS; after remediation — a clean run with zero allocation or consistency errors confirms the fix.
  • If the error recurs only during shrink operations, avoid DBCC SHRINKFILE/SHRINKDATABASE on this database going forward, as frequent shrinking increases fragmentation and the likelihood of empty non-leaf page states.