Starting with SQL Server 2012, the SSIS Project Deployment system was introduced.  If you right click on the Integration Services Catalogs folder, you can “Create Catalog” and the SSISDB is born.

I think this has been a great addition to SSIS and makes deploying SSIS packages a lot easier.  Still there are a few things I would change.  The first thing I would change is the retention period and the second is the process to clean up old data.

The retention period defaults to 365 days.  I am not sure why they (Microsoft) choose this default, but it has caused me a lot of headaches.  To get to this property, go to Integration services catalogs and then right click the SSISDB and select properties.

NOTE: if you find you cannot edit this dialog because your SSISDB has gotten into an unusable state you can update this property directly.

select * from [catalog].[catalog_properties]
update [catalog].[catalog_properties]
 set property_value = '7'
 where property_name = 'RETENTION_WINDOW'

Often, the SSIS packages I maintain, are on a schedule where they run hundreds sometimes thousands of times in a day.  This can cause a problem sooner or later when the SSISDB grows to a size where you either run out of disk space or it has trouble trying to autogrow the SSISDB (Error 0x80131904 – Timeout expired). 

This leads to the second thing I would change: the cleanup job.  Once you get an SSISDB that is really large it becomes almost impossible to delete records in it.  First the cleanup_server_retention_window proc depends on DELETE CASCADE triggers.  Second the tables themselves are not indexed well for deletion.  I have seen the existing cleanup job fail because SSIS jobs are running. 

I found this article by Mark Tassin to be very helpful in developing a better way to clean up the SSISDB tables.  His process runs faster and can be running at the same time as SSIS jobs.

NOTE: If you do use this solution and you just replace the first step in the SSIS Server Maintenance Job you do need to grant Mark’s new proc cleanup_server_retention_window_bottomup execute rights to the ##MS_SSISServerCleanupJobUser## user in the SSISDB.

NOTE: if you do use Mark’s proc you may want to pass in the Retention window with sql code something like this:

declare @retention_window_length int
select @retention_window_length = cast(property_value as int) from [catalog].[catalog_properties] where property_name = 'RETENTION_WINDOW'
exec [internal].[cleanup_server_retention_window_bottomup] @retention_window_length = @retention_window_length

NOTE: I found in some cases it was helpful to reduce the @batch_size when calling Mark’s proc.

The bottom line is I like the SSISDB, but I highly suggest you looking into these two changes to make sure you don’t end up with a SSISDB headache down the road.