Objective
This write-up explains the process of cleaning the execution history of SQL Server Integration Services packages from SSISDB.
My Assumption
In my environment, I had several packages deployed and had been running 24/7 continuously every day. I thought the default job, i.e., "SSIS_Server_Maintenance_Job" was there already to take care of SSISDB database growth.
Issue
But after a few months, I noticed that the size of SSISDB had grown to be huge!
Next, I started to understand what exactly the default job, provided by Microsoft, is doing. Well, it's not cleaning all the eligible history (as I expected) but just doing the deletion only for a few operations (around first 10 operations)!!!
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/c407fb47-e6b5-4e61-a20d-c86d814df0a0/how-to-clean-the-ssisdb-size?forum=sqldatabaseengine
Recommendations
I Googled a few more websites and came across a few suggestions...
- http://www.ssistalk.com/2013/01/31/ssis-2012-catalog-indexing-recommendations/
- https://devjef.wordpress.com/2015/02/03/ssisdb-why-bigger-isnt-always-better/
- http://thinknook.com/truncate-ssis-catalog-database-operation-log-tables-2012-11-03/
But all the proposals were leading me to do some updates on the Database which I didn't want to/wasn't allowed to do in my environment. So, what next?
Customized Solution
I decided to understand the SSISDB database model and finally, implemented the SSIS package to delete all the operational data based on the user-defined retention window. The solution has been uploaded to this blog. It contains source code, deployment document, SQL Agent job, release notes, and SSISDB database relational models as well.
Technical Details
- Database: SQL Server 2012
- IDE: SQL Server Data Tools 2012
SSISDB Database Model
If the image is not clear, then don't worry about that; you can find it in the attached zip too.
Solution
Project Parameters
Package Variables
ControlFlow
First, to get the retention window value to identify the falling operation IDs within the limit -
Looping through all OperationIDs one by one and clearing starting from child tables till the parent table. I think, there is no need to explain in detail because the below flow diagram is doing the job already!
After clean-up operation, we are invoking two default stored procedures provided by Microsoft. These are invoked just to do it the same way the default job "SSIS_Server_Maintenance_Job" does!
Conclusion
After deploying the solution, the first run, it took a lot of time because it had to clean-up all the operations piled-up over the years in SSISDB.
From the second run, it's smooth, efficient, and I noticed no disturbance to other package executions.