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

by

Troubleshooting Guide: Error 2564 — DBCC SHRINKFILE Aborted for MEMORYOPTIMIZEDDATA Containers

Why This Error Happens

This error occurs because SQL Server 2025 does not allow DBCC SHRINKFILE to operate directly on filegroups containing MEMORY_OPTIMIZED_DATA, since these containers use a checkpoint file pair (CFP) architecture rather than traditional data pages. The shrink operation relies on page-level relocation, which is structurally incompatible with the append-only, log-structured storage mechanism used by In-Memory OLTP filegroups. Attempting to shrink these files directly will always abort with Error 2564, regardless of available disk space or permissions.

Step-by-Step Resolution Guide

Step 1: Identify the Memory-Optimized Filegroup

  • Run the following query to confirm the container is tied to a MEMORY_OPTIMIZED_DATA filegroup:
  • SELECT name, type_desc FROM sys.filegroups WHERE type = 'FX';
  • Confirm the file ID reported in the error message matches a container under this filegroup using sys.database_files.

Step 2: Use Garbage Collection Instead of Shrink

  • Memory-optimized checkpoint files are reclaimed automatically via the background garbage collection (GC) thread, not manual shrink commands.
  • Force a manual checkpoint to trigger cleanup of stale CFPs:
  • CHECKPOINT;
  • Verify offline checkpoint files are being merged by querying sys.dm_db_xtp_checkpoint_files and checking the state_desc column for UNDER CONSTRUCTION or MERGED SOURCE states.

Step 3: Perform a Database-Level Backup and Restore (If Space Must Be Reclaimed)

  • If physical disk space must be reclaimed immediately, back up the database:
  • BACKUP DATABASE [YourDB] TO DISK = 'D:\Backups\YourDB.bak';
  • Restore it to a new filegroup location, which rebuilds the memory-optimized containers at an optimized size:
  • RESTORE DATABASE [YourDB] FROM DISK = 'D:\Backups\YourDB.bak' WITH MOVE 'YourDB_FG_mod' TO 'E:\SQLData\YourDB_mod';

Step 4: Drop and Recreate the Filegroup (Advanced Scenario)

  • If the container has accumulated excessive unused space due to heavy OLTP churn, consider migrating data to a newly created memory-optimized filegroup:
  • ALTER DATABASE [YourDB] ADD FILEGROUP [FG_New] CONTAINS MEMORY_OPTIMIZED_DATA;
  • Migrate memory-optimized tables to the new filegroup, then drop the old one once empty.

Alternative or Verification Steps

  • Confirm resolution by re-running DBCC SHRINKFILE on standard (non-memory-optimized) files only, and verify no Error 2564 appears in the SQL Server error log or Event Viewer under Applications and Services Logs > MSSQLSERVER.
  • As an alternative, monitor container growth trends using sys.dm_db_xtp_checkpoint_files over time instead of attempting manual shrink operations, since automatic merge/GC cycles typically resolve space bloat without intervention.