March 25, 2015 at 1:49 pm
HI,
I am trying to gain some space using 'page' compression the table size got smaller and free space got bigger (that what I wanted) BUT Database size (.mdf) grew same amount!!!! Is this normal? shouldn't I use this method to gain some space (drive level)?
March 25, 2015 at 3:00 pm
Not automatically. When pages are freed from a table, they are basically as marked as "not in use" within that db, but they remain in that db and are not released to the disk subsystem. If you think about it, that's the only way SQL could really work, because otherwise it would constantly be releasing space to, and pulling space back from, the disk subsystem.
You'll need to shrink the relevant file(s) to release the space back to the disk. Since that can -- but does not always -- lead to index fragmentation, you'll need to check fragmentation after the shrink.
Since that's an rather involved process, as you can see, you're probably better off compressing all the indexes you want to compress before shrinking any files.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
March 25, 2015 at 5:51 pm
ScottPletcher thanks for you reply. Here's the thing I am trying to do. when I do 'page' compression on a table I see almost 50% space 'savings'. But I do see that almost 50% that 'savings' is added to disk system!!! Now what do I need to do to claim the 50% 'saving' in the disk system?
Example:
.mdf file size = 100 GB
Mytable size = 5GB and free space 1 GB
After page compression 'MyTable' size = 2.5 GB and free space 2GB.
.mdf file size = 102 GB
Is this normal behaviour of the 'page compression'? one thing to be noted here the table has 'uniqeidentifier' and varchar max datatype.
March 26, 2015 at 9:01 am
Roughly normal, yes. The rebuild process requires additional disk space.
But, after the rebuild, more of that 102G is unallocated and could, theoretically, be released to disk by shrinking the file.
You can run this command from within that db to see how much total space and used space exists in each non-log file; thus, by inference, you know how much unused space there is in the file:
DBCC SHOWFILESTATS
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
March 27, 2015 at 5:25 am
I may not have explained correctly. when I do 'page' compression (heap table) I see 50% compression, just say 5GB gain after compression.
Now around 5 GB increase to the 'unallocated' space (when I run sp_spaceused) that is what I wanted. BUT same time .mdf file size also increase to around 5GB. So what's the point doing 'page' compression?
Is there any way I can just increase 'unallocated' space by 5 GB not increasing .mdf file size?
March 27, 2015 at 8:04 am
Not absolutely, but the best chance is to specify "SORT_IN_TEMPDB = ON" when you rebuild the index for compression. That will SQL will use tempdb for the work space instead of the main db.
Btw, you can't compress a heap, so I assume you meant it was a heap before you created a compressed clustered index to cause the table to compress.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply