Beware the SSIS Server Maintenance Job

Microsoft-SQL-Server-2012

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.

 

Advertisements

1 thought on “Beware the SSIS Server Maintenance Job”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s