SSISDB Maintenance job cleaning data very slow on SQL Server 2014

Question

On SQL Server 2014 Microsoft (12.0.2430) SQL Server Enterprise: Core-based Licensing (64-bit) after changing the retention period from 365 to 90 since last 2 days job clean up about 2 GB of data only. I have rebuild & update all stats on SSISDB database still its slow.

Current Size of database is 344GB Approx. what is the best way to clean old records Quickly, To do that do i need down time ?

I have followed below article so far :- https://www.mssqltips.com/sqlservertip/3307/managing-the-size-of-the-sql-server-ssis-catalog-database/

Answer 1

In order to do it quickly, you will need to delete the SSISDB and recreate it. But I don't recommend doing that. The cleanup job is interfering with your execution of SSIS Packages (or vice-versa).

My approach when I'm trimming back the retention period is to walk backwards a day at a time. The script below will do that, just change the @DesiredRetention to what you want and it will walk backwards to that point. You can stop and start this script multiple times (to allow for it to run when packages are not) and it will pick up where it left off.

DECLARE @DesiredRetention INT = 30;
DECLARE @CurrentRetention INT = (SELECT property_value FROM SSISD.internal.catalog_properties WHERE property_name = 'RETENTION_WINDOW');

WHILE @CurrentRetention > @DesiredRetention
BEGIN

    PRINT 'Removing History older than: ' + CAST(@CurrentRetention AS VARCHAR(10));
    PRINT GETDATE();

    --Change the desired retention period...
    EXEC [SSISDB].[catalog].[configure_catalog] @property_name=N'RETENTION_WINDOW', @property_value=@CurrentRetention;

    --Run the Cleanup Job
    EXEC [SSISDB].[internal].[cleanup_server_retention_window];

    --Decrement the value.
    SET @CurrentRetention = @CurrentRetention - 1;

END

--Make sure to set the desired retention period...
EXEC [SSISDB].[catalog].[configure_catalog] @property_name=N'RETENTION_WINDOW', @property_value=@DesiredRetention;