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

by

Troubleshooting Guide: Error 2513 — DBCC DBREINDEX Not Supported on Memory-Optimized Tables

Why This Error Happens

Error 2513 occurs because DBCC DBREINDEX is a legacy rebuild command that only works against traditional disk-based tables and their B-tree indexes. Memory-optimized tables in SQL Server 2025 use a completely different storage engine (Hekaton) with hash and range indexes that do not have “pages” or “fragmentation” in the conventional sense, so they cannot be reorganized or rebuilt using disk-based indexing commands. SQL Server throws this error as a hard block to prevent the engine from attempting an unsupported physical operation on an in-memory structure.

Step-by-Step Resolution Guide

Step 1: Identify the Memory-Optimized Table Triggering the Error

  • Run the following query to confirm which table(s) are memory-optimized:
  SELECT name, is_memory_optimized 
  FROM sys.tables 
  WHERE is_memory_optimized = 1;
  • Cross-reference the table name from your failed DBCC DBREINDEX script against this result set.
  • Note whether the table uses a NONCLUSTERED HASH index or a NONCLUSTERED range index, as this affects your remediation path.

Step 2: Remove or Replace the Unsupported DBCC Command

  • Open the maintenance script, job step, or stored procedure where DBCC DBREINDEX is being called.
  • Delete or comment out the line referencing the memory-optimized table, for example:
  -- DBCC DBREINDEX ('dbo.MyMemoryOptimizedTable') -- Remove this line
  • If the script loops through all tables in the database, add a filter to exclude memory-optimized tables:
  WHERE t.is_memory_optimized = 0

Step 3: Apply the Correct Maintenance Strategy for In-Memory Tables

  • For hash indexes, monitor and manage bucket count instead of rebuilding:
  SELECT * FROM sys.dm_db_xtp_hash_index_stats;
  • If bucket count is suboptimal, you must drop and recreate the index (or the table) with a revised BUCKET_COUNT, since hash indexes cannot be “reindexed” in place.
  • For range indexes, no rebuild is required — the Hekaton engine self-manages the Bw-tree structure automatically.
  • Update SQL Agent jobs (via SSMS: SQL Server Agent > Jobs > [Job Name] > Steps) to skip memory-optimized tables entirely in index maintenance plans.

Step 4: Update Ola Hallengren or Custom Maintenance Scripts (If Applicable)

  • If using Ola Hallengren’s IndexOptimize procedure, verify you are on a version that natively excludes memory-optimized tables (v2022.01 or later recommended for SQL Server 2025 compatibility).
  • Set the @MemoryOptimized parameter appropriately, or manually confirm the WHERE clause excludes sys.tables.is_memory_optimized = 1.

Alternative or Verification Steps

  • Re-run your maintenance job or script manually in SSMS and confirm Error 2513 no longer appears in the Messages tab or SQL Agent job history.
  • As an alternative check, query sys.dm_db_xtp_object_stats to confirm the memory-optimized table’s internal statistics are updating normally without manual reindexing intervention.