Compress an entire database

By Bob Duffy16. September 2011 04:30

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 

My simpler script to just do compression at the table level is here

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 [' + + '] ON ' else 'TABLE ' END +  ' [' + + '].[' + 
+ case when ds.type='PS' then convert(varchar,sp.partition_number) else 'ALL' end 
+ 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,, si.type, si.index_id

Leave a Reply