SQL Server Integration Services Catalog Best Practices

By:   |   Comments (2)   |   Related: > Integration Services Best Practices


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?


The best practices described in this tip are valid for SQL Server 2012 till the latest version.

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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on MSSQLTips.com. Koen has been awarded the Microsoft MVP data platform award for many years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Wednesday, August 1, 2018 - 9:40:46 AM - Koen Verbeeck Back To Top (76928)

Hi Neal,

when you change the default, all already configured packages will keep the current logging level.
For example, if you have a SQL Server Agent job step executing packages and they are all configured with the default logging level, they will continue to use the default logging level even when you changed the server wide setting. It's only at the creation of new job steps that the new settings are applied. So you'll have to update existing jobs to work with the new logging level.

Regarding your second question: I assume so. I don't have an instance right now where I can test, but the log maintenance jobs should get rid of old records.

Monday, July 23, 2018 - 5:21:22 PM - Neal Erdmann Back To Top (76794)

Hello Koen,

I've set the Server-wide Default Logging Level to None but I still see the addition of records in the catalog.event_messages view.† So, I have a couple of questions.†

First, when the change was made from Basic to None for Server-wide Default Logging Level does the change take immedidately or does the instance need to be restarted in order for the change to take.

Second, am I correct in assuming that the number of records seen in the catalog.event_messages view should stop and eventually decrease as the SSIS Server Maintenance Job runs each day?

Thanks for your help and your time,


get free sql tips
agree to terms