Sql-server – After page compressing a huge table, the disk space didn’t change. Do I need to shrink in any kind

compressionshrinksql server

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

Best Answer

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.index_id
    ,i.NAME AS index_name
    ,p.partition_number
    ,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
Related Question