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 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.
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.
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.
In the Catalog properties dialog, you can change the Server-wide Default Logging Level to any 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:
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:
- It’s a slow process
- 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.
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.
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.
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.
You can roll back to a previous version by selecting it from the list and clicking Restore to Selected Version.
Next Steps
- Take a look at the SSIS catalogs in your environments and double check if they are configured correctly.
- For more information about the catalog logging levels, please check out the tip Logging Level Recommendations for the SQL Server Integration Services Catalog.
- For more SQL Server 2016 tips, you can use this overview.
