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

by

Why This Error Happens

Error 5220 occurs when SQL Server attempts to relocate an Index Allocation Map (IAM) page during operations like ALTER INDEX ... REBUILD or DBCC SHRINKFILE, but the page cannot be moved due to underlying allocation corruption or a conflicting lock held by another process. This typically indicates that the database’s physical structure has inconsistencies, often caused by abrupt shutdowns, storage-level corruption, or hardware failures affecting the data file. It can also surface when shrink operations run concurrently with heavy write transactions, causing allocation contention on the IAM page.

Step-by-Step Resolution Guide

Step 1: Verify Database Integrity

  • Open SQL Server Management Studio (SSMS) 21 or higher (compatible with SQL Server 2025).
  • Run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to scan for allocation and consistency errors.
  • Review the output for specific page IDs matching the one reported in Error 5220.

Step 2: Isolate and Halt Conflicting Processes

  • Check for active shrink or rebuild operations using sys.dm_exec_requests to confirm no overlapping transactions are targeting the same file.
  • Temporarily pause automated maintenance plans (via SQL Server Agent) that trigger index rebuilds or shrink operations.
  • On Windows Server 2025, open Task Manager to ensure no third-party backup agents are locking the .mdf/.ndf files.

Step 3: Repair or Rebuild the Affected Index

  • If DBCC CHECKDB confirms corruption, attempt ALTER INDEX [IndexName] ON [TableName] REBUILD WITH (ONLINE = ON); to force a clean rebuild of the allocation structure.
  • If the index cannot rebuild online, take the database into single-user mode via ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE; before attempting repair.
  • As a last resort, run DBCC CHECKDB ('YourDatabaseName', REPAIR_ALLOW_DATA_LOSS) only after a verified backup exists, as this may discard corrupted rows.

Step 4: Validate Storage Health

  • Navigate to Windows Server 2025 Event Viewer (Windows Logs > System) and check for Disk or NTFS-related warnings (Event ID 7 or 153) coinciding with the SQL error timestamp.
  • Run chkdsk C: /f /r (replace with the correct volume) during a maintenance window to rule out underlying disk sector issues.

Alternative or Verification Steps

  • Re-run the original operation (index rebuild or DBCC SHRINKFILE) that triggered Error 5220; if it completes without reporting the IAM page error, the fix was successful.
  • If the error persists, restore the database from the last known good backup to a test environment and compare DBCC CHECKDB results to confirm the corruption originated in production rather than from a transient disk I/O issue.