A tip to decrease the storage size of SSISDB is to apply data compression to the 4 largest tables:
(this feature is only available in the Enterprise-edition of SQL Server)
USE [SSISDB]
ALTER TABLE [internal].[event_messages] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)
ALTER TABLE [internal].[operation_messages] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)
ALTER TABLE [internal].[execution_component_phases] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)
ALTER TABLE [internal].[execution_data_statistics] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)
In my SSISDB it saved me approximately -85% on [internal].[event_messages]:
Don’t forget to create indexes on your SSISDB:
https://clinthuijbers.wordpress.com/2014/08/14/ssisdb-2012-catalog-indexing-recommendations/
Pingback: SSISDB 2012 – Catalog Indexing Recommendations | Clint Huijbers' Blog
Hi Clint,
Do you know if applying compression on SSISDB tables is officialy supported by microsoft ?
Hmm…I wouldn’t advice that. I did create a few indexes to speed up the reporting though