November 8, 2010 at 7:15 am
Is there any scripts out in this forum that automatically do compression when i restore db's from 2005 to 2008.
I belive if do page compression there is not need to do row compression,so looking for page compression script that does on all tables immediately after the restore.
November 8, 2010 at 10:35 am
Enabling compression on every single table blindly? That's a pretty bad idea. Remember that compression will ALWAYS increase CPU overhead, and won't always reduce space used. Adding page level compression to every table in your db is going to cause a huge performance hit.
You should manually look at your tables and see which ones are the best compression candidates, and then do just those.
November 8, 2010 at 10:35 pm
Tara-1044200 (11/8/2010)
I belive if do page compression there is not need to do row compression
Correct. Page compression includes row compression. The trade-off of compression is additional CPU overhead for less IO.
You should take a look at the following article about compression:
http://msdn.microsoft.com/en-us/library/dd894051(SQL.100).aspx
November 9, 2010 at 6:35 am
Derrick
I understand your concern and my database has only 5 tables which are huge and all 5 of them are condidates for compression as i am restoring a 2005 database on 2008 server i am getting more than 50% cut in the size in my test and so i would like implement this through a job so that after the restore it also kick off compression. does it make any sense?
November 9, 2010 at 9:23 am
Tara-1044200 (11/9/2010)
DerrickI understand your concern and my database has only 5 tables which are huge and all 5 of them are condidates for compression as i am restoring a 2005 database on 2008 server i am getting more than 50% cut in the size in my test and so i would like implement this through a job so that after the restore it also kick off compression. does it make any sense?
Can't you just script out 5 lines then, and execute them after you restore?
Enabling compression is a really simple command..
ALTER TABLE dbo.TableName
REBUILD WITH (DATA_COMPRESSION = PAGE)
November 9, 2010 at 10:04 am
David Levy recently posted a script that should work for what you are trying to accomplish.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply