Sql-server – SQL Server – moved index behind PK constraint to different filegroup, but space not freed

indexprimary-keysql serversql-server-2008

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

Best 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
Related Question