For people running SQL Server Integration Services (SSIS) in SQL Server 2012, there may be a fairly big issue that Phil Brammer writes about in his article. The issue is with the “SSIS Server Maintenance Job”, that by default attempts to clean up old data, but could cause issues if you aren’t aware of exactly how it works.
The entire SSISDB catalog is linked together via foreign keys, most of which are all linked to a single ancestor table – internal.operations. Almost all descendants of that table are set to cascade delete when removing data from internal.operations. 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.
You can read Phil’s entire article and see if you are having the same issue.