1

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/

2

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;
  • When i run query using days retention its finishing in couple of seconds. Also just fix TYPO SSISDB DesiredRetention INT = 90; CurrentRetention INT = (SELECT property_value FROM SSISDB.internal.catalog_properties WHERE property_name = 'RETENTION_WINDOW'); – – AJ's SQL Jul 25 '16 at 15:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.