By Bob Duffy16. September 2011 04:30http://www.facebook.com/widgets/like.php?href=http://blogs.prodata.ie/post/Compress-and-entire-database.aspx
I posted a script earlier to generate the script to compress an entire database. here is one to do the compress/rebuild partition by partition. one thing to bear in mind is that ONLINE rebuild is not supported when rebuilding partition by partition. See http://technet.microsoft.com/en-us/library/ms188388.aspx
My simpler script to just do compression at the table level is here
http://blogs.msdn.com/b/boduff/archive/2010/02/23/how-good-is-data-compression-in-sql-2008.aspx
So I think the best advice is “if” you can go offline, then do it a partition at a time. if you cannot, then do the entire table and accept the longer process.
/*
Script to Produce Compression Statements
Notes: Can change MAXDOP and only select one type of index if needed
*/
select 'ALTER ' + case when si.type =1 then 'INDEX [' + si.name + '] ON ' else 'TABLE ' END + ' [' + s.name + '].[' + o.name
+ '] REBUILD PARTITION='
+ case when ds.type='PS' then convert(varchar,sp.partition_number) else 'ALL' end
+ ' WITH (DATA_COMPRESSION=PAGE, MAXDOP=8, FILLFACTOR=100'
+ case when ds.type='FG' then ' ,ONLINE = ON' else '' end -- Cannot REBUILD INDEX ONLINE for a partition
+'); '
from sys.indexes si
inner join sys.objects o on o.object_id =si.object_id
inner join sys.schemas s on s.schema_id=o.schema_id
inner join sys.partitions sp on si.index_id =sp.index_id and sp.object_id =o.object_id
inner join sys.data_spaces ds on ds.data_space_id =si.data_space_id
where si.type>0 and o.type='U'
/* comment these out as appropriate if you only want to do one type of index or one table*/
--and si.type_desc ='CLUSTERED'
--and si.type_desc ='NONCLUSTERED'
--and o.object_id=object_id('DatabaseLog')
order by s.name, o.name, si.type, si.index_id
