I have a large table Foo. There are 4 filegroups (FG1, FG2, FG3, FG4)
FG2 is on a disk that is nearly full. It has 2 indexes:
a) clustered index (on Date) b) a unique non-clustered index (on ID)
There is a PK constraint on the table, on ID.
I wanted to move index (b) from FG2 to a different file group - FG4.
First I tried simply builiding a second index, like (b), on FG4, then dropping (b). This failed - An explicit DROP INDEX is not allowed on index due to the PK.
So I went ahead and rebuilt it on the new filegroup:
CREATE UNIQUE NONCLUSTERED INDEX PK_Foo ON Foo(ID) WITH (DROP_EXISTING = ON, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, IGNORE_DUP_KEY = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [FG4]
This succeeded. I can see the index is now definitely on FG4, all is well. However, no space has been freed up on FG2 at all. If I try to shrink FG2, it shows no space within it is free.
Where has my space gone?
Update - Index sys.data_spaces:
FileGropName ObjectName IndexType IndexName type_desc Mb
FG3 Foo NONCLUSTERED IX_Foo_CorrelationId IN_ROW_DATA 193802
FG3 Foo NONCLUSTERED IX_Foo_DeviceIntId IN_ROW_DATA 97676
FG3 Foo NONCLUSTERED IX_Foo_DrtId_RC_CorrelationId IN_ROW_DATA 286386
FG2 Foo CLUSTERED IX_Foo_LastResponse IN_ROW_DATA 468845
FG4 Foo NONCLUSTERED PK_Foo IN_ROW_DATA 104377