The SSISDB database is automatically created when we create SSIS catalog. All the SSIS projects, packages and execution history are saved in this database. So if you have a package that run every few minutes you could see the database size is growing exponentially. I deployed one project at AWS EC2 server with limited disk storage size (100GB SSD), the package was automatically executed every 3 minutes by SQL Agent Job. After one weeks later, I noticed the disk size almost full. After investigation, I notice the size of SSISDB is very big already. How can I control SSISDB to a minimum size?
SSIS created a maintenance job already when we create the catalog, it’s SIS Server Operation Records Maintenance.
Looking the job steps, you will notice it calls a stored procedure internal.cleanup_server_retention_window, in this stored procedure you will see it is using catalog.catalog_properties to do database maintain.
Now is solution is easy, I just need to update RETENTION_WINDOW property of catalog_properties .
exec catalog.configure_catalog RETENTION_WINDOW , 1
After this change, then just waiting for SSIS Maintenance Job to do database cleaning task.
If you don’t want to do this via T-SQL. You can right click on your catalog then click Property to change settings from Catalog Properties window:
After you reduced Database size, you maybe will notice the log file size is going up now. For the log file, if it’s not very critical system, you can used DBCC SHRINKFILE to shrink files directly:
USE SSISDB;
GO
–use below script to find ssisdb log file id
–SELECT file_id, name
FROM sys.database_files;
— Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE SSISDB
SET RECOVERY SIMPLE;
GO
— Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (2, 1);
GO
— Reset the database recovery model.
ALTER DATABASE SSISDB
SET RECOVERY FULL;
GO