Question :
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?
Thanks!
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
Answer :
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