On SQL Server the database SSISDB is created when Integration Services is installed. This holds the configuration for the SSIS instance, package information and records information about executions. By default it is configured to retain 365 days of data and 10 versions of projects. This may be fine for some SSIS instances but unnecessary for others - resulting in a very large database.
The current config can be listed by the following command:
SELECT * FROM [SSISDB].[internal].[catalog_properties]
Result:
|
property_name |
property_value |
|
ENCRYPTION_ALGORITHM |
AES_256 |
|
MAX_PROJECT_VERSIONS |
10 |
|
OPERATION_CLEANUP_ENABLED |
TRUE |
|
RETENTION_WINDOW |
365 |
|
SCHEMA_BUILD |
11.0.7462.6 |
|
SCHEMA_VERSION |
2 |
|
SERVER_LOGGING_LEVEL |
1 |
|
SERVER_OPERATION_ENCRYPTION_LEVEL |
2 |
|
VERSION_CLEANUP_ENABLED |
TRUE |
Changing the config can be done with the following commands (changing the values to match your requirements).
EXEC catalog.configure_catalog RETENTION_WINDOW, 90
EXEC catalog.configure_catalog MAX_PROJECT_VERSIONS, 5
Once the values have been updated you can execute the job created to called "SSIS Server Maintenance Job" using the command below:
EXEC msdb.dbo.sp_start_job "SSIS Server Maintenance Job"
It may take a while to run if you have a lot of data to delete but should be scheduled to run daily to keep removing new data and keeping the database at a managable size.