MSSQL: There is a daily partitioned table which was 2 GB and we needed to compress this table by page compression. After compression, the table size got smaller if I check from the table properties - storage. But the disk space didn't change a bit after this compression. I need to gain some space on the disc and wonder if shrinking any kind (file, database) would help.

I would like to hear your advises. Thanks

  • Is there more free space in the database files after you compressed the pages? If you need to free up disk space you need to run dbcc shrinkfile (filename, x) with the X being the size you want to shrink it to. This command will fragment your indexes and I've found it's faster if you run it multiple times with small shrinks each time. It's also bad if you plan to have your DB grow in the future since it can result in file system fragmentation if your DB grows a little at a time. – Alen Jul 12 '18 at 13:20
  • Since the table is partitioned, I can't find the filegroup of it in order to shrinkfile. – Deniz Jul 12 '18 at 13:25

In order to reclaim disk space, you would have to use SHRINKFILE. The usual caveats about incurred data fragmentation and the need to rebuild indexes to resolve that fragmentation apply.

Here is a sample query to determine which partitions are on which filegroups. (Borrowed from Determining Filegroup for a Table)

SELECT OBJECT_SCHEMA_NAME(t.object_id) AS schema_name
    ,t.NAME AS table_name
    ,i.NAME AS index_name
    ,fg.NAME AS filegroup_name
    ,FORMAT(p.rows, '#,###') AS rows
FROM sys.tables t
INNER JOIN sys.indexes i
    ON t.object_id = i.object_id
INNER JOIN sys.partitions p
    ON i.object_id = p.object_id
        AND i.index_id = p.index_id
LEFT OUTER JOIN sys.partition_schemes ps
    ON i.data_space_id = ps.data_space_id
LEFT OUTER JOIN sys.destination_data_spaces dds
    ON ps.data_space_id = dds.partition_scheme_id
        AND p.partition_number = dds.destination_id
INNER JOIN sys.filegroups fg
    ON COALESCE(dds.data_space_id, i.data_space_id) = fg.data_space_id
ORDER BY p.partition_number
| improve this answer | |

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.