Home
SSISDB becomes too large
Published: Thursday, 26 July 2018 | Categories: SQL Server | Keywords: SSIS | Status: Posted | Created: Thursday, 26 July 2018 | Author: paul

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.

 


Add Comment
No Comments.