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

by

Troubleshooting Guide: Error 11404 – ALTER TABLE SWITCH Blocked by Indexed View Reference

Why This Error Happens

This error occurs because the target table in your ALTER TABLE...SWITCH operation is referenced by one or more indexed (materialized) views, and SQL Server cannot guarantee data consistency for the view’s indexed data during a partition switch. SQL Server 2025 requires that indexed views be dropped or disabled before a SWITCH operation because the engine cannot automatically update the indexed view’s physical storage structure when partition metadata changes instantaneously. This is a metadata-safety restriction, not a corruption or permissions issue.

Step-by-Step Resolution Guide

Step 1: Identify the Indexed Views Referencing the Table

  • Run the following query to find all indexed views dependent on the target table:
SELECT 
    v.name AS ViewName,
    i.name AS IndexName
FROM sys.views v
INNER JOIN sys.indexes i ON v.object_id = i.object_id
INNER JOIN sys.sql_expression_dependencies d ON v.object_id = d.referencing_id
WHERE d.referenced_id = OBJECT_ID('YourTargetTableName')
    AND i.index_id > 0;
  • Note the schema name, view name, and index name(s) returned for use in later steps.
  • Confirm these are clustered indexes on views (true “indexed views”) and not just standard views.

Step 2: Drop or Disable the Indexed Views Temporarily

  • Script out the full CREATE VIEW and CREATE INDEX definitions before dropping anything (use SSMS “Script View as DROP and CREATE” or sp_helptext).
  • Drop the clustered index on the view first, then drop the view if required:
DROP INDEX IX_IndexedViewName ON dbo.YourIndexedView;
  • Alternatively, disable the index instead of dropping (only works in some SWITCH scenarios):
ALTER INDEX IX_IndexedViewName ON dbo.YourIndexedView DISABLE;

Step 3: Execute the ALTER TABLE SWITCH Operation

  • Re-run your original partition switch command:
ALTER TABLE YourTargetTableName SWITCH PARTITION 1 TO YourStagingTableName PARTITION 1;
  • Confirm the command completes without Error 11404.
  • Check sys.dm_db_partition_stats to validate partition row counts moved correctly.

Step 4: Recreate the Indexed Views and Rebuild Indexes

  • Restore the indexed view using the script captured in Step 2:
CREATE VIEW dbo.YourIndexedView WITH SCHEMABINDING AS ...
GO
CREATE UNIQUE CLUSTERED INDEX IX_IndexedViewName ON dbo.YourIndexedView (Column1);
  • If you only disabled the index, re-enable and rebuild it instead:
ALTER INDEX IX_IndexedViewName ON dbo.YourIndexedView REBUILD;
  • Validate index fragmentation is within acceptable thresholds using sys.dm_db_index_physical_stats.

Alternative or Verification Steps

  • Verify the fix by querying sys.indexes on the view object to confirm index_id > 0 exists again and is_disabled = 0.
  • If dropping/recreating the view is not feasible due to size, consider restructuring the ETL process to use INSERT...SELECT with batching instead of SWITCH, avoiding indexed view conflicts entirely.