SQL SERVER – Huge Size of SSISDB – Catalog Database SSISDB Cleanup Script

In recent past, while doing Comprehensive Database Performance Health Check I encountered an interesting issue. In this blog, I am sharing my knowledge about how to clean up the SSISDB database using the script.

Before you try this, make sure you have gone through below blog to check the symptoms.

SQL SERVER – Huge Size of Table catalog.execution_component_phases in SSISDB

If you have taken care of SSISDB configuration parameters, then proceed further. There is another script on the internet which modifies the system object and I don’t recommend using it.

NOTE: Below script is going to clean up ALL LOGGING INFORMATION from SSISDB database. Please make sure that you take backup of the database before playing with it. Since you might be deleting huge data, transaction log file (LDF) size for this database would grow drastically. Be prepared for that.

USE SSISDB
GO
delete from [internal].[executions] 
GO
delete from [internal].[executable_statistics]
GO
delete from [internal].[execution_component_phases] 
GO
delete from [internal].[execution_data_statistics] 
GO
delete from [internal].[execution_data_taps] 
GO
delete from [internal].[execution_parameter_values]
GO
delete from [internal].[execution_property_override_values]
GO
delete from [internal].[extended_operation_info]
GO
delete from [internal].[operation_messages]
GO
delete from [internal].[event_messages]
GO
delete from [internal].[event_message_context]
GO
delete from [internal].[operation_os_sys_info]
GO
delete from [internal].[operation_permissions]
GO
delete from [internal].[validations]
GO

In my client’s case [internal].[executable_statistics] and [internal].[operation_messages] had most number of rows.

Here are a few more steps to do some more cleanup.

EXEC SSISDB.internal.cleanup_server_log
GO
EXEC SSISDB.catalog.configure_catalog @property_name='SERVER_OPERATION_ENCRYPTION_LEVEL', @property_value='2'
GO
ALTER DATABASE SSISDB SET MULTI_USER
GO
EXEC SSISDB.internal.Cleanup_Server_execution_keys @cleanup_flag = 1
GO

Once above is completed and huge data might have been deleted. This would cause LDF size to grow a lot. You can take log backup of the database and shrink the file. One of my earlier blogs contains a neat trick. You can read it using below link.

SQL SERVER – Taking Backup Without Consuming Disk Space

Please comment and let me know if the blog was useful.

Reference: Pinal Dave (https://blog.sqlauthority.com)