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

by

Troubleshooting Guide: Error 2565 – DBCC DBREINDEX Not Supported on Memory-Optimized Tables

Why This Error Happens

Error 2565 occurs because DBCC DBREINDEX is a legacy T-SQL command that only supports disk-based tables and has no execution path for memory-optimized (In-Memory OLTP) tables. Memory-optimized tables use hash and nonclustered range indexes that are maintained entirely in memory and rebuilt only when the database goes through recovery, so the concept of a traditional “offline rebuild” does not apply to them. SQL Server 2025 blocks the command outright rather than attempting a partial or unsupported operation, which triggers this error to prevent index corruption or undefined behavior.

Step-by-Step Resolution Guide

Step 1: Confirm the Table Is Memory-Optimized

  • Run the following query to check the table’s storage type:
  • SELECT name, is_memory_optimized FROM sys.tables WHERE name = 'YourTableName';
  • A result of 1 in the is_memory_optimized column confirms this is the root cause of Error 2565.
  • Check SQL Server Management Studio (SSMS) object explorer: memory-optimized tables display a distinct icon with a small “M” marker.

Step 2: Replace DBCC DBREINDEX with Supported Alternatives

  • For hash indexes, you cannot “reindex” — instead, adjust the BUCKET_COUNT using ALTER TABLE ... ALTER INDEX REBUILD is also unsupported; you must drop and recreate the index with a new bucket count.
  • Example: ALTER TABLE YourTableName DROP INDEX YourHashIndex;
  • Followed by: ALTER TABLE YourTableName ADD INDEX YourHashIndex HASH (YourColumn) WITH (BUCKET_COUNT = 100000);
  • For nonclustered indexes on memory-optimized tables, simply drop and recreate them since there is no “rebuild” equivalent.

Step 3: Address Index Fragmentation Differently

  • Since memory-optimized indexes don’t fragment the same way disk-based B-tree indexes do, use sys.dm_db_xtp_index_stats to monitor expired rows and index efficiency instead of fragmentation percentage.
  • Run: SELECT * FROM sys.dm_db_xtp_index_stats WHERE object_id = OBJECT_ID('YourTableName');
  • If bucket count is suboptimal (too many hash collisions), this is the real “fix” — not reindexing.

Step 4: Update Maintenance Scripts and Jobs

  • Search SQL Agent jobs, maintenance plans, and any scheduled scripts for references to DBCC DBREINDEX on memory-optimized tables.
  • Replace those calls with conditional logic that checks is_memory_optimized and routes to the drop/recreate index pattern from Step 2.
  • Save the updated script and redeploy via SQL Server Agent or your deployment pipeline.

Step 5: Validate Index Design at the Schema Level

  • Review BUCKET_COUNT values set during table creation — a poorly sized bucket count is often the underlying reason someone attempted a reindex in the first place.
  • Use sys.dm_db_xtp_hash_index_stats to check for excessive chain lengths, and resize the index if avg_chain_length is high.

Alternative or Verification Steps

  • Verify the fix by re-running your maintenance script or job; it should complete without raising Error 2565, and the index should show improved avg_chain_length or reduced empty buckets in sys.dm_db_xtp_hash_index_stats.
  • If drop/recreate is not feasible due to uptime constraints, consider migrating the index to a disk-based table as an alternative, or schedule the operation during a maintenance window using ALTER TABLE with minimal downtime.