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

by

Why This Error Happens

Error 10604 occurs because SQL Server 2025 cannot simultaneously convert a statistics object into an index while using DROP_EXISTING when ONLINE = ON is also specified in the same statement. This is a known architectural limitation because the ONLINE rebuild mechanism requires an existing index structure to perform concurrent DML operations, but a statistic object lacks the underlying B-tree structure needed for the online operation to track. Essentially, the Storage Engine cannot “upgrade” a lightweight statistics blob into a fully indexed structure while keeping the table available for concurrent reads/writes.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting T-SQL Syntax

  • Open SQL Server Management Studio (SSMS) or Azure Data Studio connected to your Windows Server 2025 instance.
  • Locate the specific CREATE INDEX statement that includes both WITH (DROP_EXISTING = ON, ONLINE = ON).
  • Check if the object being replaced (DROP_EXISTING target) is actually a statistic (visible via sys.stats) rather than an index (visible via sys.indexes).

Step 2: Separate the Operation into Two Distinct Transactions

  • Remove the ONLINE = ON argument from the initial creation statement.
  • Run the index creation as an OFFLINE operation first to successfully convert the statistic into a physical index:
  CREATE INDEX IX_YourIndex ON dbo.YourTable(YourColumn) 
  WITH (DROP_EXISTING = ON, ONLINE = OFF);
  • This forces an exclusive schema lock momentarily but successfully builds the underlying index structure required for future online operations.

Step 3: Schedule the Initial Build During a Maintenance Window

  • Since ONLINE = OFF will briefly lock the table (Sch-M lock), schedule this specific conversion step during a low-traffic window to avoid blocking production queries.
  • Use sp_whoisactive or the Activity Monitor in SSMS to confirm no long-running blocking transactions are queued before executing the script.

Step 4: Apply Online Maintenance for Future Rebuilds

  • Once the statistic has been successfully converted to a physical index (Step 2), all subsequent maintenance (rebuilds/reorganizations) can safely use ONLINE = ON.
  • Update your maintenance plans or Ola Hallengren scripts to use ONLINE = ON only for existing indexes, bypassing the conflict.

Alternative or Verification Steps

  • Verification: Run SELECT * FROM sys.indexes WHERE name = 'IX_YourIndex'; after Step 2 to confirm the object type has successfully changed from a statistic to a type_desc of NONCLUSTERED or CLUSTERED.
  • Alternative: If downtime is strictly prohibited, manually drop the statistic using DROP STATISTICS dbo.YourTable.YourStatName; and then create the index as a brand new object using CREATE INDEX ... WITH (ONLINE = ON), bypassing the DROP_EXISTING clause entirely.