Troubleshooting Guide: SQL Server Error 5216 – Heap Page Could Not Be Moved

Why This Error Happens

This error occurs when SQL Server’s background heap compaction or ghost cleanup process attempts to relocate a heap page, but the underlying table metadata has already been dropped or invalidated before the operation completed. It typically arises from a race condition between a DROP TABLE (or TRUNCATE) statement and an in-progress background maintenance task, such as deferred drop cleanup or heap defragmentation. This can also surface after abrupt transaction rollbacks or in tempdb when temporary objects are dropped while system threads still reference their page allocations.

Step-by-Step Resolution Guide

Step 1: Check SQL Server Error Log and System Health Session

• Open SQL Server Management Studio (SSMS) and navigate to Management > SQL Server Logs.
• Look for correlating entries near Error 5216, especially references to DBCC CHECKDB, ghost cleanup, or deferred drop processes.
• Run the following query to inspect the system health extended events session:

  SELECT * FROM sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL);

Step 2: Run DBCC CHECKDB to Validate Database Consistency

• Execute a full consistency check against the affected database:

  DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;

• Review output for allocation errors tied to the Page ID referenced in the error (%d:%d format = FileID:PageID).
• If corruption is confirmed, note whether it is isolated to a single heap or spans multiple objects.

Step 3: Clear Deferred Drop Queue and Rebuild Heap

• Force SQL Server to process any pending deferred drops by running a checkpoint and background task flush:

  CHECKPOINT;
  EXEC sp_configure 'background drop cleanup', 1;
  RECONFIGURE;

• If the table still exists but shows heap fragmentation issues, rebuild it explicitly:

  ALTER TABLE YourTableName REBUILD;

• For tempdb-related occurrences, restart the SQL Server service to clear residual tempdb heap allocations:

  Restart-Service -Name 'MSSQLSERVER' -Force

Step 4: Apply Latest Cumulative Update for SQL Server 2025

• Verify your current build number:

  SELECT @@VERSION;

• Compare against the latest Cumulative Update (CU) listed on the Microsoft SQL Server release notes page.
• Install the latest CU via Windows Server 2025 Server Manager or directly through the standalone installer, then reboot the server.

Alternative or Verification Steps

• Re-run DBCC CHECKDB after applying fixes to confirm no further allocation errors reference the previously affected Page ID.
• If the error persists only in tempdb, schedule a full server restart during a maintenance window, as tempdb is rebuilt fresh on every SQL Server service restart, clearing any orphaned heap page references.