Problem
I have implemented the SSIS catalog that comes with SQL Server 2012 and I love the reporting and centralization that comes with it, however, I noticed after a few months that the database was growing exponentially. How can I keep the size of this database to a minimum?
Solution
The SSIS catalog is the central point for working with Integration Services (SSIS) projects that you’ve deployed to the SSIS server. When the SSIS catalog is implemented, the SSISDB database is automatically created. The scope of this tip will focus on the SSISDB growth more than the SSIS catalog itself.
SSIS projects, packages, parameters, environments, and operational history are all stored in the SSISDB database so if you have hundreds of SSIS packages or packages that run every few minutes you could see how the database storing all the historical information would grow exponentially.
Also included when you enable this feature is a SQL Server Agent Job called SSIS Server Maintenance job:
Inside this job are two steps, SSIS Server Operation Records Maintenance and SSIS Server Max Version Per Project Maintenance, that will help clean up the database. By default, this job is set to run at 12:00am nightly which is sufficient:
Looking at the first step, SSIS Server Operations Records Maintenance, you will notice that it executes a stored procedure named internal.cleanup_server_retention_window. This sounds like it could be the stored procedure that cleans up history:
Let’s browse out to the stored procedure in Management Studio and take a look at the code:
You can see from the very beginning of the stored procedure in the BEGIN TRY statement it first looks to see if Operation cleanup is enabled and if cleanup is enabled then it looks for the Retention Window:
You can also see that the stored procedure queries catalog.catalog_properties to find these values. Let’s take a look at catalog_properties:
Operation_Cleanup_Enabled is set to TRUE meaning that the package will cleanup and Retention_Window is set to 365 meaning that the package will cleanup history, etc. if it’s older than 365 days. If we have a lot of packages or a lot of run times this will probably not be sufficient unless we have a need for 365 days of report history/execution history.
To change the retention value, first you need to determine how many days you would like to keep in history, then we can run the catalog.configure_catalog stored procedure to change this value. For my example, I’ll change to 150 because I don’t need to keep anything past 150 days:
For my example, my database size is 30001.81 MB with 9850.55 MB unallocated before changing this value:
After changing the value to RETENTION_WINDOW to 150 and running the SSIS Server Maintenance job package, my unallocated space jumped up to 15699.76 MB which reduced by database size by 5849.21 MB
If we look at the second step in the job, SSIS Server Max Version per Project Maintenance, you will notice that it executes a stored procedure named internal.cleanup_server_project_version.
The SSIS Catalog keeps project versions so if you were to need to roll back to a previous version you would be able to. With that said, this sounds like it could be the stored procedure that cleans up how many project versions to keep:
Let’s browse out to the stored procedure and take a look at the code:
You can see from the very beginning of the stored procedure in the BEGIN TRY statement it first looks to see if Version Cleanup is enabled and if cleanup is enabled then it looks for the Max Project Versions:
If we take a look at catalog.properties we can see that VERSION_CLEANUP_ENABLED is set to TRUE and MAX_PROJECT_VERSIONS is set to 10. For my example, I’ll leave this alone because I don’t think the number of versions kept in the database is hurting the size so much and I would like the ability to go back in case I need to troubleshoot a package:
Next Steps
- After changing the property value to 150 from 365, the SSIS Maintenance Job took over 8 hours to complete. This may or not happen to you depending on how large your database is and how much historical information is stored in it.
- Check out more MSSQLTips.com SSIS tips here.
