0

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

  • Did you try DBCC SHRINKFILE notruncate before DBCC SHRINKFILE truncateonly? – Dean Savović Jul 10 '17 at 7:47
  • I have not actually tried to execute DBCC SHRINKFILE. Only looked at the files via SSMS, where it shows you the % free in the file, which normally works very well for me. It shows 0% free in FG2. – Nik Jul 10 '17 at 8:18
  • Ok, so your index was effectively moved to FG4, but did you really try to shrink or only "looked at the files via SSMS"? – sepupic Jul 10 '17 at 8:44
  • Other than shrinkfile, you can use this script to find out free space in your files: select name AS FileName, size/128.0 AS CurrentSizeMB, size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB from sys.database_files; – sepupic Jul 10 '17 at 8:50
  • Only looked at it in SSMS. That query shows the same: FG1 454995.625000 381132.437500 Foo_log 75073.000000 74915.898438 FG2 468976.000000 82.000000 FG3 680168.000000 102242.312500 FG4 104528.000000 149.250000 – Nik Jul 10 '17 at 8:52
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.