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

by

Why This Error Happens

Error 5247 occurs during a DBCC CHECKDB repair operation when SQL Server 2025 detects that a nonclustered (secondary) index is missing a row that exists in the underlying base (clustered or heap) table. This mismatch is typically caused by page-level corruption from storage failures, improper shutdowns, or disk I/O errors that prevented the index B-tree from staying in sync with the base table. It can also surface after bit-flip corruption on SAN/NAS storage or when antivirus/backup software locks database files mid-write.

Step-by-Step Resolution Guide

Step 1: Confirm the Scope of Corruption

  • Run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; from SQL Server Management Studio (SSMS) or sqlcmd.
  • Review the output for the specific object ID and index ID reported alongside Error 5247.
  • Check the SQL Server Error Log (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for correlating I/O warnings (Event ID 823, 824, or 825).

Step 2: Take a Full Backup Before Repair

  • Execute BACKUP DATABASE [YourDatabaseName] TO DISK = 'D:\Backups\YourDatabaseName_PreRepair.bak' WITH INIT, COMPRESSION;
  • This protects against data loss if the repair operation needs to be reversed or re-run.
  • Verify there is sufficient disk space on the target backup volume (minimum 1.5x database size recommended).

Step 3: Run Targeted Repair with Data Loss Allowance

  • Place the database in single-user mode: ALTER DATABASE [YourDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
  • Execute DBCC CHECKDB ('YourDatabaseName', REPAIR_REBUILD) WITH NO_INFOMSGS, ALL_ERRORMSGS; first, since this error is often resolved by rebuilding the index rather than deleting data.
  • If REPAIR_REBUILD does not resolve it, escalate to DBCC CHECKDB ('YourDatabaseName', REPAIR_ALLOW_DATA_LOSS) — note this may remove orphaned index entries that cannot be reconciled with the base table.
  • Return the database to multi-user mode: ALTER DATABASE [YourDatabaseName] SET MULTI_USER;

Step 4: Rebuild Affected Indexes Manually (Preferred Alternative)

  • Instead of relying solely on automated repair, manually drop and recreate the specific nonclustered index identified in Step 1 using DROP INDEX followed by CREATE INDEX ... WITH (ONLINE = ON, DATA_COMPRESSION = PAGE);
  • This avoids potential data loss from REPAIR_ALLOW_DATA_LOSS since the index is simply rebuilt from current base table data.

Step 5: Investigate Underlying Hardware/Storage Issues

  • Check Windows Server 2025 Event Viewer → Windows Logs → System for Disk or NTFS warnings correlating with the corruption timestamp.
  • Run chkdsk D: /f /r on the volume hosting the .mdf/.ndf files during a maintenance window to rule out underlying file system issues.

Alternative or Verification Steps

  • After remediation, re-run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; and confirm it returns zero allocation and consistency errors.
  • If corruption recurs on the same index, restore from the pre-repair backup and failover to a secondary replica (Always On Availability Group) if configured, then investigate the storage subsystem for persistent hardware faults.