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

by

Troubleshooting Guide: Error 1995 – Cannot Rebuild Hypothetical Index Online

Why This Error Happens

Error 1995 occurs because hypothetical indexes, created by the Database Engine Tuning Advisor (DBTA) or third-party tuning tools, contain no actual data pages and exist purely as metadata placeholders for query optimization simulations. SQL Server’s online rebuild mechanism requires valid, physical index structures to perform the row-by-row data copy and index rebuild operation, which hypothetical indexes inherently lack. This mismatch between the ALTER INDEX…REBUILD WITH (ONLINE = ON) command and the non-physical nature of the index triggers the engine to reject the operation outright.

Step-by-Step Resolution Guide

Step 1: Identify the Hypothetical Index

  • Run the following query to locate hypothetical indexes in the affected database:
SELECT name, object_id, index_id, is_hypothetical 
FROM sys.indexes 
WHERE is_hypothetical = 1;
  • Confirm the index name matches the one referenced in the Error 1995 message.
  • Check the object_id to determine which table the hypothetical index belongs to.

Step 2: Drop the Hypothetical Index Instead of Rebuilding

  • Since hypothetical indexes cannot be rebuilt (online or offline), they must be dropped using:
DROP INDEX [HypotheticalIndexName] ON [SchemaName].[TableName];
  • Verify no active tuning session (via DBTA or sp_create_stats) is still referencing this index before dropping it.
  • Close any open Database Engine Tuning Advisor sessions tied to the workload analysis that generated this index.

Step 3: Clear Residual Tuning Metadata

  • On Windows Server 2025 / Windows 11, open SQL Server Management Studio (SSMS) and connect to the instance.
  • Navigate to Tempdb and check for orphaned tuning session tables (prefixed with dbo.DTA_*) that may still reference the hypothetical index.
  • Run DBCC FREEPROCCACHE to clear cached execution plans that may still point to the dropped hypothetical index metadata.

Step 4: Recreate a Proper Physical Index (If Needed)

  • If the intended goal was performance tuning, create a real, non-hypothetical index instead:
CREATE NONCLUSTERED INDEX [IX_RealIndex] 
ON [SchemaName].[TableName] ([ColumnName])
WITH (ONLINE = ON);
  • Validate the online rebuild option is only applied to physical indexes going forward.

Alternative or Verification Steps

  • Re-run SELECT * FROM sys.indexes WHERE is_hypothetical = 1; to confirm the hypothetical index no longer appears in the results.
  • If the error persists after dropping the index, restart the SQL Server (MSSQLSERVER) service via Services.msc on Windows Server 2025 to flush any lingering metadata cache, then re-verify index state.