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/