SSISDB database Size is very large | SSIS Maintenance | How to reduce SSISDB size in SQL Server

Issue: We have face issue in dev server where SSISDB is almost 300 Gb whereas in other servers its under 50 GB. Bit googling help me understand that its due to SSIS logs not been truncated post retention of 15 days.

RCA:  SQL Server provides SSIS maintenance job to clean up old logs from SSISDB every 15 days but in my server, this job was disabled from last 1 year which causes data piled up over moths.

Solution: I have manually run maintenance scripts with an interval of a few days to avoid job running for a couple of days. Now   total size is reduced to  50 GB

I have enable Job “SSIS Server Maintenance Job” for future

How to manually run maintenance:

Step 1. Check catalog properties to identify days (by default its 10- 15) by using below query, if you directly run maintenance then it might ran for hours to days depend on old logs

select * from catalog.catalog_properties

Step2. Change retention window for data for some big no. may be 365 days and run below query. I have noticed for 10 days query takes approx. 30 mins.

exec catalog.configure_catalog RETENTION_WINDOW , 365

Step 3. Now verify again catalog properties by running below query

select * from catalog.catalog_properties

Step 4. If retention widow is fine then execute query to clear logs

EXEC [internal].[cleanup_server_retention_window]

Step 5. If this query completes in a min, means you have fewer rows, reduce retention window and re-run the query.

Repeat this process with till you reach to retention window of 10-15 days and then enable SSIS maintenance job

Over all query looks like this: