How can I clean up the SSISDB?

sql-server-2012, ssis

Solution

Phil Brammer ran into this and a host of other things related to the care and feeding of the SSIS catalog, which he covers on his post Catalog Indexing Recommendations.

Root problem

The root problem is that MS attempted to design the SSIS with RI in mind but they were lazy and allowed the cascading deletes to happen versus explicitly handling them.

Out of the box, the new SSIS 2012 catalog database (SSISDB) has some basic indexing applied, with referential integrity set to do cascade deletes between most tables.

Enter the SQL Agent job, “SSIS Server Maintenance Job.” This job by default is set to run at midnight daily, and uses two catalog parameters to function: “Clean Logs Periodically” and “Retention Period (days).” When these are set, the maintenance job purges any data outside of the noted retention period.

This maintenance job deletes, 10 records at a time in a loop, from internal.operations and then cascades into many tables downstream. In our case, we have around 3000 operations records to delete daily (10 at a time!) that translates into 1.6 million rows from internal.operation_messages. That’s just one downstream table! This entire process completely, utterly locks up the SSISDB database from any SELECT/INSERT data

Resolution

Until MS changes up how things work, the supported option is

move the maintenance job schedule to a more appropriate time for your environment

I know at my current client, we only load data in the wee hours so the SSISDB is quiet during business hours.

If running the maintenance job during a quiet period isn't an option, then you're looking at crafting your own delete statements to try to get the cascading deletes to suck less.

At my current client, we've been running a about 200 packages nightly for the past 10 months and are also at 365 days of history. Our biggest tables, by an order of magnitude are.

Schema    Table                   RowCount
internal  event_message_context   1,869,028
internal  operation_messages      1,500,811
internal  event_messages          1,500,803

The driver of all of that data, `internal.operations` only has 3300 rows in it, which aligns with Phil's comment about how exponentially this data grows.

So, identify the `operation_id` to be purged and the delete from the leaf tables working back to the core, `internal.operations` table.

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
);

DECLARE @DaysRetention int = 100;
INSERT INTO
    #DELETE_CANDIDATES
(
    operation_id
)
SELECT
    IO.operation_id
FROM
    internal.operations AS IO
WHERE
    IO.start_time < DATEADD(day, -@DaysRetention, CURRENT_TIMESTAMP);

DELETE T
FROM
    internal.event_message_context AS T
    INNER JOIN
        #DELETE_CANDIDATES AS DC
        ON DC.operation_id = T.operation_id;

DELETE T
FROM
    internal.event_messages AS T
    INNER JOIN
        #DELETE_CANDIDATES AS DC
        ON DC.operation_id = T.operation_id;

DELETE T
FROM
    internal.operation_messages AS T
    INNER JOIN
        #DELETE_CANDIDATES AS DC
        ON DC.operation_id = T.operation_id;

-- 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;

Usual caveats apply

- don't trust code from randoms on the internet

- use the diagrams from ssistalk and/or system tables to identify all the dependencies

- you might need to only segment your delete operations into smaller operations

- you might benefit by dropping RI for operations but be certain to re-enable them with the check option so they are trusted.

- consult your dba if operations last longer than 4 hours

July 2020 edit

Tim Mitchell has a good set of articles on SSIS Catalog Automatic Cleanup and A better way to Clean up the SSIS Catalog Database and his fancy new book The SSIS Catalog: Install, Manage, Secure and Monitor Your Enterprise ETL Infrastructure

@Yong Jun Kim noted in the comments

There is a chance SSIS DB might have different table names with scaleout at the end now. Instead of internal.event_message_context it can be internal.event_message_context_scaleout. Instead of internal.operations_messages, it can be internal.operations_messages_scaleout. Just modify the table names in the code accordingly, and it should run fine

This is certainly the case if you are using an SSIS IR within Azure Data Factory. You will find the "normal" tables still present but empty, with the `*_scaleout` versions containing all the data.

References

- Catalog Indexing Recommendations

- Beware the SSIS Server Maintenance Job

- Slow performance when you run the SSIS Server Maintenance Job to remove old data in SQL Server 2012

Problem

When I set this up I overlooked the retention period. My database has become pretty large so I want to decrease it's size. If I simply change the retention period (it was 365) it causes issues with SSIS running my packages. I even changed it in small increments but the deletion statement would create locks which would prevent new jobs from running. Any ideas how to work around this? I've thought about just creating a new SSISDB.

Original source