SSIS 2012 – Beware the SSIS Server Maintenance Job

Those of you running SSIS 2012 may not yet be pushing the limits of the RTM release, and while I can’t tell you where the limit boundary lies, I can give you advanced notice about a fairly big problem.

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.

With that said, what does it mean to you? Potentially the cancellation of other SSIS packages that are running at the same time. For us, each and every time the maintenance job runs, packages get canceled. This is not good.

What can you do? For starters, move the maintenance job schedule to a more appropriate time for your environment. If you are like us and you have packages running 24×7, and there is no convenient time, then what? Not much really. At that point, you can decide not to run the maintenance job and hence accumulate records in SSISDB, or you can file a case with PSS (to add weight to the case I already have open).

Some details on the issue – I suspect based on some SQL Profiler tracing that the executable running the package is timing out waiting for its lock request to insert data into an SSISDB table. This timeout is not handled well, obviously, and needs to be reworked. The product team is aware of the issue. Please let me know if you’ve run into this problem and what you’re doing about it.

For those that are curious, here is the tangled web that is the SSISBD schema:
SSIS Catalog Schema

6 thoughts on “SSIS 2012 – Beware the SSIS Server Maintenance Job

  • Pingback: SSIS 2012 – Catalog Indexing Recommendations » SSIS Talk

  • Daniel Otykier

    Hi Phil,

    My client is indeed experiencing timeout/locking issues when executing scheduled packages in the SSIS catalog on SQL2012 (11.0.3128). However, the SSIS Server Maintenance Job does not seem to interfere with any other scheduled SSIS jobs (in fact, the maintenance job runs in just a few seconds).

    Could the same problem arise from multiple SSIS jobs executing simultaneously? We didn’t have this problem before migrating the packages to the SSIS catalog.

    Best regards,
    Daniel

  • Mikah

    Thanks for the article. It’s silly how Microsoft made SSIS 2012 radically different than its predecessors, and then did their best to avoid documentation. I guess they want us to attend their training sessions 🙂

    I do have a question –
    You mention that the timeout isn’t handled well. I am having the same problem… I see no “TIMEOUT” parameter among the ones provided (like SYNCHRONIZED, DUMP_ERROR, etc.). Is there a way to extend the timeout of an SSISDB package call from within a Script Task?

    Thanks,
    -Mikah

  • Rufus

    This is a most silly feature; a maintenance job that deletes RBR freezing a whole prod server and live jobs – were these guys really serious when setting up this ETL tool?

  • Stephen

    Thank you for posting this. I am experiencing the same issue; it’s a bit silly we have to deal with a gaping hole like this in the SSIS product.

  • Pingback: Beware the SSIS Server Maintenance Job | Senior DBA

Comments are closed.