Okay so I've been condensing files off of some tiny files and moving them onto an FG that I created for these miscellaneous tables. I've been moving these tables by recreating the indexes with the
WITH (DROP_EXISTING = ON) option and it's been working great. However this last time it gave me an error. Here's the script I'm running
CREATE Unique CLUSTERED INDEX PK_MyTable ON dbo.MyTable (MyTableID, RowStatus) WITH (DROP_EXISTING = ON) ON FG_Misc;
When I run this I get the following error message.
Msg 1902, Level 16, State 3, Line 1
Cannot create more than one clustered index on table 'dbo.MyTable'. Drop the existing clustered index PK_MyTable' before creating another.
I don't see any reason why this is throwing this error on this particular table but not any others I've been working on.