How to Manage the Size of the SSISDB (Catalog Database) SSIS Tutorial 2021

 Issue: How to Manage the Size of the SSISDB (Catalog Database) SSIS Tutorial.

In this article, we are going to learn how to manage the size of the SSISDB (Catalog Database), as you guys know that we are deploying our packages to SSISDC nowadays, we are not deploying the packages to the file system anymore, so we have to take care of this database, because a lot of logins is created so there is so many information when we enable this SSISDB logs, so on each and every execution there are logs produced, that's why the database can grow huge, so we have to reduce the database by following some steps, first of all, we have to see the status of our some of the properties by following this query as shown in the picture below.


Fig-1: Query to see the status of some properties.

As you can see in the above picture that we used a query and the results are Operation_Cleanuo_enabled it is set to True, Retention_Window it is set to 365, Version_Cleanup_Enabled it is set to True, so that means I have already a maintenance job, let's create a maintenance job, we can create this job by using a query and also we can do by using Graphic user interface, I will explain the both first of all we are doing this by using the graphic user interface, right-click on the SSISDB under the Integration Services Catalog, then go to the properties, inside the properties click on the clean log periodically under the Operation log tab, then set it to ''YES'', then click on retention period tab and set it to ''90'' that means we want to keep the log file only for 90 days or you can select as per you requirement, then click on Maximum number of version per project and set it to ''5'' then click on periodically delete old versions under the project versions tab and set it to ''True'', and then click ok as shown in the picture below.


Fig-2: Maintenance job for SQL Server Integration Services Database.

The second method for creating the maintenance job is by query, you can create the job by using the below query.

EXEC catalog.configure_catalog VERSION_CLEANUP_ENABLED, TRUE

EXEC catalog.configure_catalog OPREATION_CLEANUP_ENABLED, TRUE

EXEC catalog.configure_catalog RETENTION_WINDOW, 90




Video Demo: How to Manage the Size of the SSISDB (Catalog Database) SSIS Tutorial 2021 



No comments:

Post a Comment