SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Two Things I Would Change in SSISDB Setup

By Ben Kubicek,

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.

 
Total article views: 2564 | Views in the last 30 days: 9
 
Related Articles
BLOG

How to change Integration Services Catalog (SSISDB) database Master Key encryption password?

To change the Integration Services Catalog (SSISDB) database Master Key encryption password, run the...

BLOG

SSISDB for SQL Server Integration Services

Microsoft introduced some major changes in SQL Server Integration Services with SQL Server 2012. Br...

ARTICLE

How to deploy and execute an SSIS package from the SSISDB catalog

Beginning with SQL Server 2012, SQL Server Integration Services packages can be deployed and execute...

BLOG

Reports In SSISDB

I was approached with an in shop issue where a group could not view the execution reports in the S...

FORUM

SQL Agent Job History Retention

SQL Agent Job History Retention

Tags
 
Contribute