Manage the Size of the SSISDB (Catalog Database)

The SSISDB (Catalog Database) is installed as part of the catalog configuration. With the default installation Operation cleanup is enabled, and the Retention window is set for 365 days, which means the operation records are maintained for 365 days.

With a higher Retention window, the SSISDB can grow over time and may create performance issues. To manage the size of the SSIDB, we recommend changing the retention window to a smaller value based on the business requirements.

Check the following catalog properties.

SELECT * 
FROM SSISDB.catalog.catalog_properties 
WHERE property_name IN 
                     (
                             'RETENTION_WINDOW'
                           , 'VERSION_CLEANUP_ENABLED'
                           , 'OPERATION_CLEANUP_ENABLED'
                     }

If VERSION_CLEANUP_ENABLED is set to FALSE, enable it.

EXEC catalog.configure_catalog VERSION_CLEANUP_ENABLED, TRUE EXEC catalog.configure_catalog OPERATION_CLEANUP_ENABLED, TRUE

Update the RETENTION_WINDOW to the number best suited for business. For example, if the business requirement is to retain the operation maintenance records for 100 days, update the RETENTION_WINDOW property to 100.
```
EXEC catalog.configure_catalog RETENTION_WINDOW, 100
```

Monitor SSISDB growth for a few months to determine if a change is required in the retention window. Since SSISDB is shared by all SSIS packages installed in the database, the growth of SSISDB depends on the number of packages installed on the server and the frequency of execution.

For more details, see Managing the size of the SQL Server SSIS catalog database