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.
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)
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.