Built into the SSIS catalog is a mechanism that can automatically purge log data after a set period of time. In this post, I’ll show you how to set up and manage that functionality.
SSIS catalog automatic log cleanup
The SQL Server Integration Services catalog database – SSISDB – has several dozen logging tables that are used to capture details about the administration of and execution within the SSIS catalog. These tables store the history of administrative tasks (such as deployments), the record and result of every execution, and by default, the task- and component-level detail for every single execution.
On a server that runs a lot of SSIS packages, these logs will get rather large over time. At the same time, execution log data (especially the really granular information) becomes less and less essential as it ages. Therefore, the automated log purge can be useful for removing unnecessary bloat from the SSIS execution logs.
The automated log cleanup process is turned on by default when you create the SSIS catalog. After you create the SSIS catalog, you’ll notice a new SQL Server Agent job on that server named SSIS Server Maintenance Job. This job contains two job steps:
- SSIS Server Operations Records Maintenance: For deleting old execution and operational log messages.
- SSIS Server Max Version Per Project Maintenance: For deleting old versions of projects that have been superseded by newer versions. I discussed the Max Version Per Project part of this operation in a prior post, so here we’ll just focus on the purge of the logs.
This job is scheduled to run automatically at midnight every night. Because this is just a regular SQL Server Agent job, you can adjust the schedule time or frequency as needed.
Viewing or modifying the retention settings
As I mentioned, the log purge operation is enabled by default. To view, modify, or disable, go to the SSIS catalog properties window and look under the Operations Log section.
The default settings are shown, in which the cleanup operation is enabled, and the retention period is set to 365 days. You can modify either or both of these using this window.
What happens under the hood
When the record maintenance job step runs, it invokes the SSISDB stored procedure internal.cleanup_server_retention_window. This stored procedure will retrieve the value of the Clean Logs Periodically and Retention Period settings from above. If the former is set to True and the latter is set to a value greater than zero, the delete operation will commence.
When the delete occurs, the internal.cleanup_server_retention_window stored procedure builds a working list of the operation IDs to delete, and then will delete the operations and all of their details in batches of 1,000 operations. This isn’t necessarily the most efficient delete operation, but I’ll save that topic for tomorrow’s blog post.
Should you purge log information?
We know that you can purge SSIS catalog log data, but should you? As is common, the answer is It Depends. The nature of execution logs is that their value diminishes over time. The most recent log information is the most valuable, and the oldest is the least value – if the latter has any business value at all. For most organizations, purging old SSIS logs is a good default setting. The data purist in me will always argue that there is still some residual value even in old logging information – think about comparing year-over-year execution failure rates and run times – but I’ll save that debate for another day.
Leave a Reply