I have created a table where I have around 100 partitions (per month), all of them are configured to use page compression. After that I'm inserting data to this table month by month.
I now would expect SQL Server to apply the page compression. However using sp_estimate_data_compression_savings
it doesn't look like it. (I would have savings of around 40%-50%.)
Did I miss something in configuring page compression?
The problem with this is that I have now large files, 70-80 GB per partition. When I compress them, they are half empty. Regaining this disk space is impossible because if I try to shrink the data file that is behind the partition I end up with a fragmented clustered index.
How can I avoid this disk space waste party?