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
Space in FG2 used by objects:
SELECT DS.name AS FileGropName ,OBJ.name AS ObjectName ,IDX.type_desc AS IndexType ,IDX.name AS IndexName ,AU.type_desc ,AU.total_pages * 8 / 1024 AS Mb FROM sys.data_spaces AS DS INNER JOIN sys.allocation_units AS AU ON DS.data_space_id = AU.data_space_id INNER JOIN sys.partitions AS PA ON (AU.type IN (1, 3) AND AU.container_id = PA.hobt_id) OR (AU.type = 2 AND AU.container_id = PA.partition_id) INNER JOIN sys.objects AS OBJ ON PA.object_id = OBJ.object_id INNER JOIN sys.indexes AS IDX ON PA.object_id = IDX.object_id AND PA.index_id = IDX.index_id WHERE DS.name = 'FG2' ORDER BY AU.total_pages DESC