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

by

Troubleshooting Guide: SQL Server Error 1974 — Clustered Index Disabled

Why This Error Happens

This error occurs when a clustered index has been manually disabled using an ALTER INDEX ... DISABLE statement, which renders the entire underlying table inaccessible for queries, inserts, updates, or deletes. SQL Server 2025 blocks these operations because a disabled clustered index means the table’s data pages are no longer logically organized or guaranteed consistent. This commonly happens after a DBA disables an index for maintenance (such as a bulk load or schema change) and forgets to rebuild it afterward, or when a failed index rebuild script leaves the index in a disabled state.

Step-by-Step Resolution Guide

Step 1: Identify the Disabled Index

  • Open SQL Server Management Studio (SSMS) and connect to the affected instance on your Windows Server 2025 / Windows 11 machine.
  • Run the following query to confirm the index state:
SELECT i.name AS IndexName, i.is_disabled, t.name AS TableName
FROM sys.indexes i
JOIN sys.tables t ON i.object_id = t.object_id
WHERE i.is_disabled = 1;
  • Note the exact table name and index name reported in the error message (e.g., '%.*ls' placeholders).

Step 2: Rebuild the Clustered Index

  • Execute a full rebuild to restore the index and re-enable table access:
ALTER INDEX [IndexName] ON [SchemaName].[TableName] REBUILD;
  • If the table is large, consider adding WITH (ONLINE = ON) (Enterprise edition) to minimize downtime:
ALTER INDEX [IndexName] ON [SchemaName].[TableName] REBUILD WITH (ONLINE = ON);
  • Monitor progress via sys.dm_exec_requests if the rebuild takes longer than expected.

Step 3: Check for Underlying Maintenance Scripts or Jobs

  • Review SQL Server Agent Jobs under SQL Server Agent > Jobs in SSMS for any scheduled tasks that disable indexes without a corresponding rebuild step.
  • Inspect any custom ETL or deployment scripts (e.g., SSIS packages, PowerShell deployment scripts) for DISABLE statements lacking a follow-up REBUILD.
  • Update the script logic to ensure disable/rebuild operations are wrapped in a single transaction or try/catch block to prevent orphaned disabled states.

Step 4: Validate Database Integrity

  • Run DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to rule out underlying corruption that may have triggered or resulted from the disabled index.
  • Address any reported allocation or consistency errors before resuming normal operations.

Alternative or Verification Steps

  • Re-run the original query that triggered Error 1974 to confirm the table is now accessible and returns data correctly.
  • If the rebuild fails due to insufficient disk space in tempdb (default path: C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA), free up space or redirect tempdb files to a drive with more capacity, then retry the rebuild.