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

by

Troubleshooting Guide: SQL Server Error 5209 – Dedicated Allocation Page Could Not Be Moved

Why This Error Happens

Error 5209 occurs when SQL Server’s DBCC SHRINKFILE or auto-shrink operation attempts to relocate a page that belongs to a dedicated allocation unit (such as an IAM page, GAM, SGAM, or PFS page) that cannot be physically moved within the data file. This typically happens due to internal database corruption, an interrupted shrink operation, or inconsistencies in the allocation bitmap pages caused by improper shutdowns or storage-level failures. In some cases, it also surfaces when third-party backup tools or antivirus software lock pages mid-operation, preventing SQL Server from safely reallocating them.

Step-by-Step Resolution Guide

Step 1: Verify Database Integrity First

  • Open SQL Server Management Studio (SSMS) or use sqlcmd connected to the affected instance.
  • Run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to confirm whether corruption exists beyond the allocation page issue.
  • Review the output log located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for related 824 or 5180 errors that often accompany 5209.

Step 2: Stop Any Active Shrink Operations

  • Check for running shrink jobs via sys.dm_exec_requests filtering on command = 'DbccFilesCompact'.
  • Cancel the session using KILL <session_id>; if an active shrink is stuck mid-transaction.
  • Avoid scheduling auto-shrink on databases with heavy allocation churn; disable it via ALTER DATABASE YourDatabaseName SET AUTO_SHRINK OFF;.

Step 3: Rebuild Indexes to Clear Fragmented Allocation Units

  • Execute ALTER INDEX ALL ON YourTableName REBUILD WITH (ONLINE = ON); on tables residing in the affected filegroup.
  • This forces SQL Server to reallocate extents, often resolving orphaned dedicated allocation page references.
  • If the database is in SIMPLE recovery model, ensure sufficient log space is pre-allocated to avoid autogrowth interruptions during rebuild.

Step 4: Restore From a Known Good Backup (If Corruption Confirmed)

  • If DBCC CHECKDB reports unrepairable errors tied to allocation pages, restore the database from the last verified full backup.
  • Use RESTORE DATABASE YourDatabaseName FROM DISK = 'D:\Backups\YourDatabase.bak' WITH REPLACE, RECOVERY;.
  • Apply subsequent transaction log backups to minimize data loss before resuming shrink operations.

Step 5: Validate Storage and File System Health

  • On Windows Server 2025/Windows 11, run chkdsk D: /f /r (replace D: with the volume hosting your .mdf/.ldf files) during a maintenance window.
  • Check Event Viewer under Windows Logs > System for Disk or NTFS warning events coinciding with the SQL error timestamp.
  • Confirm storage drivers and firmware for SAN/NAS-attached volumes are current, as outdated drivers can cause silent page-level write failures.

Alternative or Verification Steps

  • After remediation, re-run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS; and confirm zero allocation errors are returned before re-enabling shrink operations.
  • If error 5209 persists despite a clean CHECKDB result, attempt the shrink operation during off-peak hours using DBCC SHRINKFILE (N'YourDataFile', TRUNCATEONLY) instead of a full shrink, as this avoids moving dedicated allocation pages entirely.