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

SQL SERVER - Huge Size of SSISDB - Catalog Database SSISDB Cleanup Script antivirus 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.

Solarwinds

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)

Solarwinds
, , ,
Previous Post
SQL SERVER – Finding User Who Dropped Database Table
Next Post
SQL SERVER – 5061: FIX Error: ALTER DATABASE failed because a lock could not be placed on database ‘Database Name’. Try again later.

Related Posts

2 Comments. Leave new

  • You say that the logfile of the SSISDB will grow after executing the script, but why? I thought we are deleting some log statistics to reduce the size of SSISDB log (Get more space free).

    Reply
    • Trinity Rolling
      May 24, 2019 1:52 am

      All of these tables contain FK constraints so we have to use the DELETE statement, which in turn writes to the transaction log. You can wrap the DELETE FROM statement in a transaction and execute the DELETE statement in smaller increments and frequent commits to reduce the log file growth.

      Reply

Leave a Reply

Menu