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

by

Troubleshooting Guide: Error 5272 – IAM Page Could Not Be Moved

Why This Error Happens

Error 5272 occurs when SQL Server’s DBCC SHRINKFILE or auto-shrink operation attempts to relocate an Index Allocation Map (IAM) page but cannot obtain the required exclusive lock on the underlying table or index. This typically happens because another session holds an active transaction, a long-running query, or a schema lock on the object at the exact moment the shrink operation tries to move data pages. Concurrent DML operations (INSERT/UPDATE/DELETE), open transactions, or replication/CDC processes reading from the same object are the most common culprits blocking exclusive access.

Step-by-Step Resolution Guide

Step 1: Identify Blocking Sessions and Active Transactions

  • Open SQL Server Management Studio (SSMS) and connect to the affected instance.
  • Run sp_who2 or query sys.dm_exec_requests to find sessions with open transactions against the target database.
  • Check sys.dm_tran_locks filtered by resource_database_id to identify which object is holding the exclusive lock request hostage.
  • Note the session_id of any long-running transactions tied to the table reported in the error.

Step 2: Schedule the Shrink Operation During Low-Activity Windows

  • Avoid running DBCC SHRINKFILE or DBCC SHRINKDATABASE during business hours or peak ETL/batch processing windows.
  • On Windows Server 2025, use Task Scheduler or SQL Server Agent Jobs to trigger shrink operations during a maintenance window (e.g., 2:00 AM – 4:00 AM).
  • Example command: DBCC SHRINKFILE (N'YourDataFile_log', 10) WITH NO_INFOMSGS;
  • Ensure no overlapping index maintenance jobs (REBUILD/REORGANIZE) are scheduled at the same time, as they also require exclusive access.

Step 3: Disable or Pause Auto-Shrink if Enabled

  • Open SSMS, right-click the database → Properties → Options.
  • Locate “Auto Shrink” and set it to False (auto-shrink is not recommended in production environments).
  • Alternatively, run: ALTER DATABASE [YourDB] SET AUTO_SHRINK OFF;
  • This prevents SQL Server from attempting unpredictable shrink operations while transactions are active.

Step 4: Isolate the Object and Retry Manually

  • If a specific table is repeatedly flagged, place the database in single-user mode temporarily (only if feasible): ALTER DATABASE [YourDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
  • Re-run the shrink command manually once no other connections are active.
  • Revert back to multi-user mode immediately after: ALTER DATABASE [YourDB] SET MULTI_USER;

Step 5: Check for Replication, CDC, or Always On Availability Group Interference

  • Query sys.dm_cdc_log_scan_sessions if Change Data Capture is enabled on the table.
  • If using Always On Availability Groups, confirm the secondary replica isn’t holding a read-only lock via redo operations.
  • Temporarily pause CDC capture jobs or replication log reader agents before retrying the shrink.

Alternative or Verification Steps

  • To verify the fix, re-run the original DBCC SHRINKFILE command and confirm it completes without Error 5272 in the SQL Server Error Log (Event Viewer → Applications and Services Logs → MSSQLSERVER).
  • If the error persists, consider rebuilding the specific index with ALTER INDEX ALL ON [TableName] REBUILD; during a maintenance window instead of shrinking the entire file, as this often resolves fragmentation-related locking without requiring exclusive database-wide access.