SSISDB & Integration Services Catalogs Gotchas

With SQL Server 2012 came the new Integration Services Catalogs which to save me typing I’ll call ISC in this blog post.

In this post I’m not going to guide your through how to create the ISC, which in turn creates the SSISDB database, instead I’m going to detail a couple of configuration settings which you should pay special attention to when creating a new ISC; otherwise these configurations can cause a headache for a DBA by causing the SSIS database to grow quickly and in what appears to be a uncontrollable, this isn’t a problem but to a DBA who wasn’t expecting this to happen it can be baffling at the time.

Let’s says you’ve created your ISC and SSIS packages are executing during the day and nightly to load something like a data warehouse.  With the invention of ISC and the SSISDB Microsoft added a certain level of self-logging for the execution of the SSIS packages.  Microsoft also gave us an the ability to select what level of detail we want to log and they’ve put these into the following option.

  • None – Nothing
  • Basic – All events
  • Performance –  Only performance stats
  • Verbose – All events with the addition of including custom events

See link for more detailed descriptions  https://msdn.microsoft.com/en-GB/library/hh231191.aspx

Therefore, with the basic level (which is set by default) you can imagine if you have a large SSIS package and its running frequently then the SSISDB database (which is where all the logging data is stored)  is going to grow quickly.

We have various way of changing this logging detail level configuration in order to prevent this from happening, in this example we’re going to change the configuration at a server level.  To get there we need to first open the Integration services catalogs folder in the object explorer (see figure 1) and then right click on the SSISDB folder and then finally select the properties option.

1

Figure 1

You’ll then have the properties screen showing (see figure 2), we’re going to look at two of the configurations in here.  Firstly the Server wide default logging level configuration which by default is set at basic you can alter this by clicking and selecting from the drop down menu to your preferred level, I personally use performance at a server level and then alter the logging level at a package level or SQL Agent job level if more logging is required (I’ll show you this in a later blog post)

2

Figure 2

The second configuration which I believe is  overlooked by most people when configuring ISC is the Retention period (days)  which by default  is set at 365 days.  Now most companies I expect won’t require this amount of historical logging data therefore you need to determine what amount of data you want to retain.

Changing this setting can drastically reduce the speed at which your SSISDB database will grow and from experience if this configuration change is missed during ISC’s initial setting up, then once the SSIDB has grown to quite a large size, for example 20GB, it can be quite hard to alter this setting and remove the historical data.

The historical data is removed by a SQL agent job which is created as part of the ISC’s creation called SSIS Server Maintenance Job, however if you alter the Retention period configuration once the database is already large you could be in with a very long wait for this job to complete (that’s if it doesn’t fail).

I hope this helps as I personally fell victim to this setting when I first worked with ISC and I’ve come across countless SQL instances where people are having problems with the SSISDB growing quickly and can’t see the reason why.

Advertisements

One thought on “SSISDB & Integration Services Catalogs Gotchas

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s