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

by

Troubleshooting Guide: SQL Server Error 1771

Why This Error Happens

This error occurs because the clustered index on the referenced table has been disabled, typically after an ALTER INDEX ... DISABLE operation was executed on that index. SQL Server cannot create a foreign key relationship because a disabled clustered index renders the underlying table data inaccessible, meaning the engine cannot validate uniqueness or referential integrity against it. This commonly happens after index maintenance scripts, failed migrations, or bulk-load operations that disable indexes to improve performance but are never rebuilt afterward.

Step-by-Step Resolution Guide

Step 1: Identify the Disabled Index

  • Open SQL Server Management Studio (SSMS) and connect to the target instance on Windows Server 2025 / Windows 11.
  • Run the following query to locate disabled indexes on the referenced table:
SELECT name, is_disabled 
FROM sys.indexes 
WHERE object_id = OBJECT_ID('dbo.YourReferencedTable') 
AND is_disabled = 1;
  • Confirm the clustered index (type_desc = ‘CLUSTERED’) is the one flagged as disabled.

Step 2: Rebuild the Disabled Clustered Index

  • Execute a full rebuild command to re-enable the index and restore data access:
ALTER INDEX [YourClusteredIndexName] 
ON dbo.YourReferencedTable 
REBUILD;
  • If the index name is unknown, use ALTER INDEX ALL ON dbo.YourReferencedTable REBUILD; to rebuild every index on the table at once.
  • Monitor the Activity Monitor in SSMS to ensure the rebuild completes without blocking from other transactions.

Step 3: Re-Attempt the Foreign Key Creation

  • Navigate back to the script or GUI operation used to create the foreign key constraint.
  • Re-run the ALTER TABLE ... ADD CONSTRAINT statement for the foreign key.
  • Verify no other indexes on the referencing or referenced tables are also disabled, as this will trigger the same Error 1771 for secondary objects.

Step 4: Check for Automated Jobs Causing Recurrence

  • Open SQL Server Agent under the target instance in SSMS Object Explorer.
  • Review any maintenance plans or scheduled jobs (e.g., Index Maintenance or ETL jobs) under Jobs that use DISABLE on indexes without a corresponding REBUILD step.
  • Update the job step logic to ensure every DISABLE command is paired with a REBUILD command before the job completes.

Alternative or Verification Steps

  • Verify the fix by re-running the sys.indexes query from Step 1; the is_disabled column should now return 0 for the clustered index.
  • If rebuilding fails due to insufficient disk space (common in tempdb), move the index to a different filegroup using ALTER INDEX ... REBUILD WITH (ONLINE = ON) if running Enterprise Edition, or free up space on the tempdb volume before retrying.