SSIS 2012 Deployment Fail Part Two: SSIS Server Maintenance Job

Recently I post about a timeout issue I was getting when deploying SSIS projects.  It was mainly to do with too much log and previous versions being kept in the SSISDB on our test environments. My solution was to run the SSIS Server Maintenance Job prior to every deployment to mitigate the timeout. By and large this has been super effective.

However that job can take a considerable amount of time to run. And it is not just our test environment that has seen issues: we rely on SSIS heavily and our schedules are every 5 minutes or so, so lots and lots of logs are created.

Looking around the internet, I found an article that updates the stored proc that is run when deleting the log data by truncating it (link below). I’ve tested out the stored proc provided and it works well. It still takes a long time, like 45 minutes, but that is light speed compared to the 6 hours we were getting on a daily basis.

We don’t want this stored proc to replace the current one that is run as part of the Maintenance Job as we’d lose all ability to trace any issues.

In the comments section there is also a suggestion for indexes to create that have helped the purges of subsequent runs. It will be worth seeing if the daily job can now cope, and will keep the reports more snappy, which is no bad thing.

http://thinknook.com/truncate-ssis-catalog-database-operation-log-tables-2012-11-03/

Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Knows a thing or two about Pokémon. Knows too much about SQL Agent. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

3 thoughts on “SSIS 2012 Deployment Fail Part Two: 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