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

by

Troubleshooting Guide: Error 1926 – Cannot Create Clustered Index on Read-Only Filegroup

Why This Error Happens

This error occurs because SQL Server 2025 attempts to rebuild or move all nonclustered indexes associated with a table whenever a clustered index is created, and one or more of those nonclustered indexes physically reside on a filegroup currently marked as READ_ONLY. Since the clustered index creation process requires write access to reorganize data pages for every dependent index, the operation fails immediately upon detecting the read-only constraint. This typically happens after a filegroup has been set to read-only for archival purposes, or during a database restore where file properties retained their original read-only state.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Filegroup and Index

  • Run the following query to pinpoint which nonclustered index and filegroup are causing the conflict:
SELECT i.name AS IndexName, f.name AS FilegroupName, f.is_read_only
FROM sys.indexes i
JOIN sys.filegroups f ON i.data_space_id = f.data_space_id
WHERE i.object_id = OBJECT_ID('YourTableName');
  • Note the exact FilegroupName returned where is_read_only = 1.

Step 2: Temporarily Set the Filegroup to Read-Write

  • Open SQL Server Management Studio (SSMS) or a query window connected to the instance.
  • Execute the following T-SQL command to toggle the filegroup status:
ALTER DATABASE [YourDatabaseName] 
MODIFY FILEGROUP [YourFilegroupName] READ_WRITE;
  • Ensure no other active transactions are locking the database file during this change (check via sp_who2).

Step 3: Create or Rebuild the Clustered Index

  • With the filegroup now writable, execute the original clustered index creation script:
CREATE CLUSTERED INDEX IX_ClusteredIndexName
ON YourTableName (ColumnName);
  • Monitor the Windows Event Viewer (Applications and Services Logs > Microsoft > SQL Server) for any residual disk I/O errors during the rebuild.

Step 4: Revert the Filegroup to Read-Only (If Required)

  • If the filegroup was intentionally archived, restore its original state to prevent accidental writes:
ALTER DATABASE [YourDatabaseName] 
MODIFY FILEGROUP [YourFilegroupName] READ_ONLY;
  • Verify the physical disk location (e.g., D:\SQLData\Archive\) still has the correct NTFS permissions set for the SQL Server service account.

Alternative or Verification Steps

  • Verify the Fix: Re-run the sys.filegroups query from Step 1 to confirm is_read_only returns 0 (or 1 if you successfully reverted it) and check sys.indexes to ensure the clustered index now appears with index_id = 1.
  • Alternative Solution: If the filegroup must remain permanently read-only (e.g., compliance archive), move the specific nonclustered index to a different, writable filegroup first using CREATE INDEX ... WITH (DROP_EXISTING = ON) ON [NewFilegroup] before attempting the clustered index creation.