If you’re getting SQL Server error 1988 that reads something like “Cannot rebuild clustered index ‘PK__Employee__7AD04FF1A39ECAB1’ online because it is disabled.“, it’s probably because you’re trying to rebuild a disabled clustered index online.
We can’t rebuild a disabled clustered index with (ONLINE = ON)
.
As Microsoft’s documentation for SQL Server states:
Rebuilding a disabled clustered index cannot be performed when the ONLINE option is set to ON.
And also:
You can rebuild a disabled nonclustered index online when the table doesn’t have a disabled clustered index. However, you must always rebuild a disabled clustered index offline if you use either the
ALTER INDEX REBUILD
orCREATE INDEX WITH DROP_EXISTING
statement.
So to fix this issue, rebuild the index offline.
Example of Error
Here’s an example of code that produces the error:
ALTER INDEX PK__Employee__7AD04FF1A39ECAB1 ON Employees REBUILD WITH (ONLINE = ON);
Output:
Msg 1988, Level 16, State 1, Line 1
Cannot rebuild clustered index 'PK__Employee__7AD04FF1A39ECAB1' online because it is disabled.
In this case, I used WITH (ONLINE = ON)
to rebuild the index online, which failed. This is a clustered index, and clustered indexes can’t be rebuilt using WITH (ONLINE = ON)
.
Solution
To fix this issue we must rebuild the clustered index offline.
We can either specify it explicitly:
ALTER INDEX PK__Employee__7AD04FF1A39ECAB1 ON Employees REBUILD WITH (ONLINE = OFF);
Or we can remove the WITH (ONLINE = ON)
option altogether:
ALTER INDEX PK__Employee__7AD04FF1A39ECAB1 ON Employees REBUILD;
Either of those should work to rebuild the clustered index.
Don’t forget to re-enable any nonclustered indexes that may have been disabled at the same time that the clustered index was disabled, as well as any foreign keys that reference the clustered index’s key.