March 9, 2015 at 6:04 am
My database has TDE (database level, NOT the column level). My drive are filing up and don't have option to add another drives.
some of my tables contains data types (max) varchar.... when I run calculations from GUI I find I can save some of the spaces. Questions are:
1. Since it's TDE enable should I go ahead compress 'Page'? because I will be saving more space on 'page' compression than 'row' compression.
2. if It's OK to compress, how do I reclaim space on the drive? should I do 'shrink file'?
3. what's the best way to do row/page compression and reclaim space?
thanks
March 9, 2015 at 7:58 am
Dan121 (3/9/2015)
1. Since it's TDE enable should I go ahead compress 'Page'? because I will be saving more space on 'page' compression than 'row' compression.
1. Yes, you can compress the tables / indexes in a TDE enabled database. I have a production server with just that situation (although the tables in question were compressed before TDE was enabled) As usual, test in QA first, your mileage may vary, etc.
Dan121 (3/9/2015)
2. if It's OK to compress, how do I reclaim space on the drive? should I do 'shrink file'?
2. Yes, you would do a shrink file, although all the usual recommendation against shrinking still apply (the DB will eventually grow again, fragmentation, etc.)
Dan121 (3/9/2015)
3. what's the best way to do row/page compression and reclaim space?
3. I'd probably lean towards first enable and compress the tables / indexes in question, verify everything is still working OK and as expected (there's the potential for a performance hit,) then shrink the file, then check your indexes for fragmentation and reorganize / rebuild as needed.
March 9, 2015 at 8:52 am
Thanks Jasona.work, appreciate your reply. Last question, since I have 4 data files ( 1 .mdf, 3 .ndf), which file should I shrink? Any suggestions or best practices?
March 9, 2015 at 9:00 am
The answer to that is a bit trickier.
You need to find out which one has the table / indexes you're planning to compress and go from there. One way to get an idea of which might get you the most "bang for the buck" as far as shrinking the file, would be to run a query (there's several out on the net) to determine what the current free space in each of the files is, then determine the work load on each (is one holding tables that regularly get data loaded, then deleted as part of a data load? Don't shrink it, or it'll just need to grow again!) before shrinking.
March 9, 2015 at 11:47 am
I'd like to know a bit more about the columns in this table, given the potential max row size compression may be of no benefit at all
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 10, 2015 at 6:21 am
Thanks Perry. You are right, BLOB and Max data type..etc. cannot be compress.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply