SSIS Catalog (SSISDB) Cleanup – Revisited

Posted by paschott in SQL Server, SSIS, TSQL On October 29, 2020

I wrote about cleaning up the SSIS Catalog a while back, but needed to revisit this recently for a different use and needed something that can run in an ongoing manner. My earlier post still works, but I recently adapted some code from Tim Mitchell to create a stored procedure that can do that cleanup. Tim wrote the majority of this. I adapted it to wrap it in a stored procedure to handle varying batch sizes for the deletes as well as to ensure all of the tables are deleted in smaller sets of rows.

Here is the code to create the stored procedure. If your SSIS Catalog is not named SSISDB, adjust accordingly.


use SSISDB
GO
CREATE PROC dbo.usp_PurgeSSISCatalogLogs
@RowsToDelete int = 5000
AS
BEGIN –Proc
/*
Script name: Purge SSIS Catalog log tables
Author: Tim Mitchell (www.TimMitchell.net)
Date: 12/19/2018
Purpose: This script will remove most of the operational information from the SSIS catalog. The
internal.operations and internal.executions tables, as well as their dependencies,
will be purged of all data with an operation created_time value older than the number
of days specified in the RETENTION_WINDOW setting of the SSIS catalog.
Note that this script was created using SQL Server 2017 (14.0.3048.4). Depending on the SQL Server
version, the table and/or column names may be different.
2020-10-28 – Adjusted script to use loops for all tables and parameterize the @Rows to delete
Peter A. Schott (schottsql.com)
*/
SET NOCOUNT ON
DECLARE @enable_purge BIT
DECLARE @retention_period_days SMALLINT
DECLARE @rows INT = 1
/*
Query the SSIS catalog database for the retention settings
*/
SELECT @enable_purge = CONVERT(bit, property_value)
FROM [catalog].[catalog_properties]
WHERE property_name = 'OPERATION_CLEANUP_ENABLED'
SELECT @retention_period_days = CONVERT(int, property_value)
FROM [catalog].[catalog_properties]
WHERE property_name = 'RETENTION_WINDOW'
/*
If purge is disabled or the retention period is not greater than 0, skip the remaining tasks
by turning on NOEXEC.
*/
IF NOT (@enable_purge = 1 AND @retention_period_days > 0)
SET NOEXEC ON
/*
Get the working list of execution IDs. This will be the list of IDs we use for the
delete operation for each table.
*/
IF (OBJECT_ID('tempdb..#executions') IS NOT NULL)
DROP TABLE #executions
SELECT execution_id
INTO #executions
FROM catalog.executions
WHERE CAST(created_time AS DATETIME) < DATEADD(DAY, 0 – @retention_period_days, GETDATE())
/***************************************************
internal.executions and its dependencies
***************************************************/
SET @rows = 1
WHILE @rows > 0
BEGIN
DELETE TOP (@RowsToDelete) tgt
FROM internal.event_message_context tgt
INNER JOIN internal.event_messages em
ON em.event_message_id = tgt.event_message_id
INNER JOIN #executions ee
ON ee.execution_id = em.operation_id
SET @rows = @@ROWCOUNT
END
RAISERROR ('Deleted data from internal.event_message_context', 10, 1) WITH NOWAIT
/*
internal.event_messages
*/
SET @rows = 1
WHILE @rows > 0
BEGIN
DELETE TOP (@RowsToDelete) tgt
FROM internal.event_messages tgt
INNER JOIN #executions ee
ON ee.execution_id = tgt.operation_id
SET @rows = @@ROWCOUNT
END
RAISERROR ('Deleted data from internal.event_messages', 10, 1) WITH NOWAIT
/*
internal.executable_statistics
*/
SET @rows = 1
WHILE @rows > 0
BEGIN
DELETE TOP (@RowsToDelete) tgt
FROM internal.executable_statistics tgt
INNER JOIN #executions ee
ON ee.execution_id = tgt.execution_id
SET @rows = @@ROWCOUNT
END
RAISERROR ('Deleted data from internal.executable_statistics', 10, 1) WITH NOWAIT
/*
internal.execution_data_statistics is one of the larger tables. Break up the delete to avoid
log size explosion.
*/
SET @rows = 1
WHILE @rows > 0
BEGIN
DELETE TOP (@RowsToDelete) tgt
FROM internal.execution_data_statistics tgt
INNER JOIN #executions ee
ON ee.execution_id = tgt.execution_id
SET @rows = @@ROWCOUNT
END
RAISERROR ('Deleted data from internal.execution_data_statistics', 10, 1) WITH NOWAIT
/*
internal.execution_component_phases is one of the larger tables. Break up the delete to avoid
log size explosion.
*/
SET @rows = 1
WHILE @rows > 0
BEGIN
DELETE TOP (@RowsToDelete) tgt
FROM internal.execution_component_phases tgt
INNER JOIN #executions ee
ON ee.execution_id = tgt.execution_id
SET @rows = @@ROWCOUNT
END
RAISERROR ('Deleted data from internal.execution_component_phases', 10, 1) WITH NOWAIT
/*
internal.execution_data_taps
*/
SET @rows = 1
WHILE @rows > 0
BEGIN
DELETE TOP (@RowsToDelete) tgt
FROM internal.execution_data_taps tgt
INNER JOIN #executions ee
ON ee.execution_id = tgt.execution_id
SET @rows = @@ROWCOUNT
END
RAISERROR ('Deleted data from internal.execution_data_taps', 10, 1) WITH NOWAIT
/*
internal.execution_parameter_values is one of the larger tables. Break up the delete to avoid
log size explosion.
*/
SET @rows = 1
WHILE @rows > 0
BEGIN
DELETE TOP (@RowsToDelete) tgt
FROM internal.execution_parameter_values tgt
INNER JOIN #executions ee
ON ee.execution_id = tgt.execution_id
SET @rows = @@ROWCOUNT
END
RAISERROR ('Deleted data from internal.execution_parameter_values', 10, 1) WITH NOWAIT
/*
internal.execution_property_override_values
*/
SET @rows = 1
WHILE @rows > 0
BEGIN
DELETE TOP (@RowsToDelete) tgt
FROM internal.execution_property_override_values tgt
INNER JOIN #executions ee
ON ee.execution_id = tgt.execution_id
SET @rows = @@ROWCOUNT
END
RAISERROR ('Deleted data from internal.execution_property_override_values', 10, 1) WITH NOWAIT
/*
internal.executions
*/
SET @rows = 1
WHILE @rows > 0
BEGIN
DELETE TOP (@RowsToDelete) tgt
FROM internal.executions tgt
INNER JOIN #executions ee
ON ee.execution_id = tgt.execution_id
SET @rows = @@ROWCOUNT
END
RAISERROR ('Deleted data from internal.executions', 10, 1) WITH NOWAIT
/***************************************************
internal.operations and its dependencies
***************************************************/
/*
internal.operation_messages
*/
SET @rows = 1
WHILE @rows > 0
BEGIN
DELETE TOP (@RowsToDelete) tgt
FROM internal.operation_messages tgt
INNER JOIN #executions ee
ON ee.execution_id = tgt.operation_id
SET @rows = @@ROWCOUNT
END
RAISERROR ('Deleted data from internal.operation_messages', 10, 1) WITH NOWAIT
/*
internal.extended_operation_info
*/
SET @rows = 1
WHILE @rows > 0
BEGIN
DELETE TOP (@RowsToDelete) tgt
FROM internal.extended_operation_info tgt
INNER JOIN #executions ee
ON ee.execution_id = tgt.operation_id
SET @rows = @@ROWCOUNT
END
RAISERROR ('Deleted data from internal.extended_operation_info', 10, 1) WITH NOWAIT
/*
internal.operation_os_sys_info
*/
SET @rows = 1
WHILE @rows > 0
BEGIN
DELETE TOP (@RowsToDelete) tgt
FROM internal.operation_os_sys_info tgt
INNER JOIN #executions ee
ON ee.execution_id = tgt.operation_id
SET @rows = @@ROWCOUNT
END
RAISERROR ('Deleted data from internal.operation_os_sys_info', 10, 1) WITH NOWAIT
/*
internal.validations
*/
SET @rows = 1
WHILE @rows > 0
BEGIN
DELETE TOP (@RowsToDelete) tgt
FROM internal.validations tgt
INNER JOIN #executions ee
ON ee.execution_id = tgt.validation_id
SET @rows = @@ROWCOUNT
END
RAISERROR ('Deleted data from internal.validations', 10, 1) WITH NOWAIT
/*
internal.operation_permissions
*/
SET @rows = 1
WHILE @rows > 0
BEGIN
DELETE TOP (@RowsToDelete) tgt
FROM internal.operation_permissions tgt
INNER JOIN #executions ee
ON ee.execution_id = tgt.[object_id]
SET @rows = @@ROWCOUNT
END
RAISERROR ('Deleted data from internal.operation_permissions', 10, 1) WITH NOWAIT
/*
internal.operations
*/
SET @rows = 1
WHILE @rows > 0
BEGIN
DELETE TOP (@RowsToDelete) tgt
FROM internal.operations tgt
INNER JOIN #executions ee
ON ee.execution_id = tgt.operation_id
AND tgt.start_time IS NOT NULL
SET @rows = @@ROWCOUNT
END
RAISERROR ('Deleted data from internal.operations', 10, 1) WITH NOWAIT
SET NOEXEC OFF
END –Proc

view raw

usp_PurgeSSISCatalogLogs.sql

hosted with ❤ by GitHub

 

About Author

paschott

I’ve been working with SQL Server for quite some time. Along the way, I’ve learned quite a few things and realized that I won’t ever know everything about SQL Server. I intended to keep growing and learning to be able to do my job well and share my experiences with others. I currently work for a Health-related non-profit based in Boise as a Database Architect.

Leave a Reply

Your email address will not be published. Required fields are marked *