Azure Data Factory SSISDB - Why is it so big?

In this blog, we will see steps to maintain the size of the SSISDB

In one of our client’s environments, SSIS packages started to fail as the SSISDB’s log is full. Upon checking the size of the database, we found that it had grown by 220 GB in 1 month i.e. almost 7 GB per day which is huge.

After a quick check on larger objects within the database, we found that the following three tables were occupying most of the space

1. event_message_context_scaleout

2. event_messages_scaleout

3. operation_messages_scaleout

“After cleaning up logs, we were able to shrink the database to 5 GB (before shrinking, the database was at 250 GB)"

1. event_message_context_scaleout

2. event_messages_scaleout

3. operation_messages_scaleout”

SSISDB in Azure

You can utilize an integration runtime (IR) for Azure-SQL Server Integration Services (SSIS) that has been provisioned in Azure Data Factory (ADF) or Synapse Pipelines to execute SSIS packages placed in:

• Managed instance of Azure SQL Database hosting the SSIS catalogue (SSISDB) (Project Deployment Model)

• a file system, Azure Files, or a Microsoft SQL Managed Instance (MSDB)-hosted SQL Server database (Package Deployment Model)

By connecting to SSISDB, which is hosted by your Azure SQL Database server/Managed Instance, using SQL Server Management Studio (SSMS), you can specify SSISDB log clean-up properties to handle SSIS package execution logs. Once connected, you may expand the Integration Services Catalogs node on the Object Explorer window of SSMS, right-click on the SSISDB subnode, and choose the Properties menu item to launch the Catalog Properties dialogue box. The following SSISDB log clean-up properties are available on the dialogue box for catalogue properties:

• Clean Logs Periodically: By default set to True, this option enables the periodic cleanup of package execution logs.

• Retention Period (days): This parameter specifies the maximum age of retained logs (in days), and is by default set to 365. Older logs are destroyed when the corresponding SSISDB stored procedure is called.

• Periodically Remove Old Versions: This option, which by default is set to True, enables the cleanup of stored project versions.

• Maximum Number of Versions per Project: Defines the maximum number of saved project versions, which is by default set to 10, with older versions being erased when the necessary SSISDB stored procedure is called. The default value is 10, and previous versions are deleted.

Once SSISDB log clean-up properties are configured, you can invoke the relevant SSISDB stored procedure, [internal].[cleanup_server_retention_window_exclusive], to clean up SSIS package execution logs.

To clean up SSIS job logs, you can invoke the relevant SSISDB stored procedure, [internal].[cleanup_completed_jobs_exclusive]. The retention period is by default set to 60 minutes and older logs are deleted when the stored procedure is invoked.

To clean up SSIS IR operation logs, you can invoke the relevant SSISDB stored procedure, [internal].[cleanup_expired_worker]. The retention period is by default set to 168 hours and older logs are deleted when the stored procedure is invoked.

Automatic SSISDB log cleanup using ADF or Synapse Pipelines

ADF may be used to automatically and periodically clean up SSISDB logs whether you host SSISDB on an Azure SQL database server or a Managed Instance. You can accomplish this by setting up an Execute SSIS Package activity in an ADF pipeline with an embedded package that contains a single Execute SQL Task that calls the pertinent SSISDB stored procedures.

SSISDB logs can be automatically cleaned up using the Azure SQL Managed Instance Agent.

If you use Azure SQL Managed Instance to host SSISDB, you can also use the Azure SQL Managed Instance Agent, which is a built-in job orchestrator and scheduler, to automatically and regularly clear out SSISDB logs. We've also designed a T-SQL job named SSIS Server Maintenance Job under Azure SQL Managed Instance Agent to particularly clean up SSIS package execution logs if SSISDB was recently created in your Azure SQL Managed Instance. It is by default turned off and set to run every day on a schedule. By utilizing SSMS to connect to your Azure SQL Managed Instance, you can change the schedule or activate it as needed.