Cleanup script for the SSISDB Catalog to make up for MS' poor performing query and high default days for history retention.

SSISDB Cleanup.sql

--With thanks to Bill Fellows for the script from here:
-- http://stackoverflow.com/questions/21781351/how-can-i-clean-up-the-ssisdb
USE SSISDB;

SET nocount ON;

IF Object_id('tempdb..#DELETE_CANDIDATES') IS NOT NULL
  BEGIN
      DROP TABLE #delete_candidates;
  END;

CREATE TABLE #delete_candidates
  (
     operation_id BIGINT NOT NULL PRIMARY KEY
  );

--Adjust accordingly. If you've been set at the default of 365 days, you probably want to start close to that upper limit.
--Adjust downward in appropriate increments for your environment. 10-20 at a time is usually a good start.
DECLARE @DaysRetention INT = 350;

INSERT INTO #delete_candidates
            (operation_id)
SELECT IO.operation_id
FROM   internal.operations AS IO
WHERE  IO.start_time < Dateadd(day, -@DaysRetention, CURRENT_TIMESTAMP);



WHILE EXISTS (SELECT * FROM internal.event_message_context AS emc JOIN #delete_candidates AS dc ON dc.operation_id = emc.operation_id)
BEGIN -- delete event_message_context
DELETE TOP(4500) T
FROM   internal.event_message_context AS T
       INNER JOIN #delete_candidates AS DC
               ON DC.operation_id = T.operation_id;
CHECKPOINT
END -- delete event_message_context

WHILE EXISTS (SELECT * FROM internal.event_messages AS e JOIN #delete_candidates AS dc ON dc.operation_id = e.operation_id)
BEGIN --Delete event_messages
DELETE TOP(4500) T
FROM   internal.event_messages AS T
       INNER JOIN #delete_candidates AS DC
               ON DC.operation_id = T.operation_id;
CHECKPOINT
END --Delete event_messages

WHILE EXISTS (SELECT * FROM internal.operation_messages AS o JOIN #delete_candidates AS dc ON dc.operation_id = o.operation_id)
BEGIN --Delete operation_messages
DELETE TOP(4500) T
FROM   internal.operation_messages AS T
       INNER JOIN #delete_candidates AS DC
               ON DC.operation_id = T.operation_id;
CHECKPOINT
END --Delete operation_messages

--etc
--Finally, remove the entry from operations
DELETE T
FROM   internal.operations AS T
       INNER JOIN #delete_candidates AS DC
               ON DC.operation_id = T.operation_id;
GO
CHECKPOINT
GO