Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Integration Services Catalog Best Practices


By:   |   Read Comments   |   Related Tips: More > Integration Services Best Practices

Attend these FREE MSSQLTips webcasts >> click to register


Problem

Iím starting a new project using Integration Services (SSIS) 2016. I will use the project deployment model. I was wondering if there are any best practices regarding the configuration of the SSIS catalog?

Solution

The Integration Services catalog is the central repository for the storing, validating and executing SSIS packages and projects. It was introduced in SQL Server 2012 and is supported by the project deployment model. The catalog can be found inside the SQL Server database engine.

ssis catalog - Description: ssis catalog

Although the node has the name ďIntegration Services CatalogsĒ, you can only create one catalog per database engine. To do this, you need to enable CLR. The catalog is created on top of a user database called SSISDB. However, the catalog and the corresponding databases are created with some defaults which are best changed after creation. In this tip, weíll go over these defaults and discuss better configuration options.

SSISDB database - Description: SSISDB database

Default Logging Level of the SSIS Catalog

Besides storing the SSIS projects, the catalog is responsible for the logging of the various events that occur during the execution of a package. The granularity of the logging is controlled by logging levels. The default logging level - Basic, already logs a larger number of events. This means if you execute lots of packages, there is some overhead for the logging and the SSISDB database will grow quickly. If you are working on SQL Server 2016 or later, you can create a custom logging level and control the amount of logging. For example, you can log only errors and warnings. If you are on SQL Server 2012 or 2014, you might want to choose the Performance logging level, which logs errors, warnings and performance statistics.

The default logging level can be changed by right-clicking on the catalog and select Properties.

catalog properties - Description: catalog properties

In the Catalog properties dialog, you can change the Server-wide Default Logging Level to any logging level.

default logging level - Description: default logging level

In new executions, this logging level will now be chosen unless specified otherwise. While creating new SQL Server Agent Jobs, the default logging level will be used as well. For more information about the catalog logging levels, check out the tip Logging Level Recommendations for the SQL Server Integration Services Catalog.

SSIS Catalog Default Log Retention

With the logging levels, we can control how much logging there is in the catalog. However, you can also configure how long you want to keep the logging records in the SSISDB database. You can find the log retention setting in the catalog properties window:

ssis log retention - Description: ssis log retention

The default value is 365, which means an entire year worth of logging. If you use the default logging level Basic, this can lead to massive amounts of logging data if you have many packages executing throughout the day. You probably donít need to keep that much logging around. Typically, a month of logging should suffice for most troubleshooting scenarios.

If the property Clean Logs Periodically is set to True, a scheduled SQL Server Agent Job will clean out log records older than the Retention Period setting. You can also manually kick off the SQL Server Agent Job to clean the log, for example right after you set the retention period to a smaller value. Be aware though that lots of deletes are executed in the SSISDB database through foreign key relationships and the  delete on cascade option. This has two consequences:

  1. Itís a slow process
  2. Your transaction log size can blow up in size. If you have a full year of logging and set the retention period to 30, 11 months of data must be deleted. Iíve seen cases where the transaction log grew quickly to over 25GB.

To avoid having the SSISDB transaction log fill your entire disk, it is recommended to set the retention period to a low value and delete in small increments.  For example, you change the setting from 365 to 320. You clean the log. You set it to 300. You run the cleanup job again. You set it to 280.  Repeat until you reach your desired setting.

ssis maintenance job - Description: ssis maintenance job

SSISDB Recovery Model

Even if you control the amount of logging through a logging level and the retention period of the logging, the SSISDB database can still grow to an unexpected size. The default recover model of the SSISDB database is the Full Recovery Model. This means the transaction log will keep growing until the disk is full or until a transaction log backup is taken and the log is truncated. However, the SSISDB is usually not part of a maintenance plan Ė although itís a good idea to take backups of your SSIS projects Ė and most of the time transaction log backups are not taken of this database.

The consequence is the transaction log file keeps growing because itís never truncated. In combination with a bad default logging level and a big retention window, the SSISDB can grow to a large sizes. Iíve seen cases where the SSISDB database was the biggest database on the server, with a log file of over half a terabyte. All of this can easily be fixed by setting the recovery model to Simple, which can be done in the Database Properties window.

simple recovery model - Description: simple recovery model

NOTE: The SSISDB is created by restoring a backup; it is not created from scratch. This means settings of the model database donít have an effect on the SSISDB database. In other words, even if the model database is configured to use the Simple Recovery Model, the SSISDB will end up using the Full Recovery Model, which is why this setting can slip under the radar of the administrator.

SSIS Default History of Project Versions

Every time you deploy a project to the SSIS catalog, the previous version of the project is kept. You can consider this a simplified versioning of the project. By default, the catalog keeps 10 versions of a project. If you have very large projects with lots of packages, this also impacts the size of the SSISDB database. Unless you deploy multiple times a day, 10 versions are not needed. Typically, three historical versions should suffice for any rollback scenario where you wish to reverse the deployment of a project.

You can change the number of versions the catalog keeps for a project in the Catalog Properties.

catalog versions - Description: catalog versions

Like the log retention, the versions log is cleaned out by the SQL Server Agent maintenance job. You can find the versions for a specific project by right-clicking on a project and selecting Versions.

ssis project versions - Description: ssis project versions

You can roll back to a previous version by selecting it from the list and clicking Restore to Selected Version.

restore ssis project - Description: restore ssis project
Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools