How do I clean SSISDB running on Azure SQL Database?

Question

I'm using the SSISDB created through Azure Data Factory SSIS runtime (on a Azure SQL Database) and I would like to stick with the Basic tier.
Hence I'm executing the cleanup procedures from time-to-time.
However, these procedures are not cleaning the database enough, even when I configure the retention setting to the bare minimum it still keeps using over 1.3 GB.
This size after cleaning is increasing slowly to a size that would require me to upgrade the DB tier.

I'm able to run almost all the Stored Procedures on SSISB with the name [internal].[cleanup_***], except for the [internal].[cleanup_server_log].

The Stored Procedure, 'cleanup_server_log', failed to run because the Integration Services database (SSISDB) is not in single-user mode. In SQL Server Management Studio, launch Database Properties dialog box for SSISDB, switch to the Options tab, and set the Restrict Access property to single-user mode (SINGLE_USER). Then, try to run the stored procedure again.

How can/should I clean the SSISDB that's an Azure SQL Database?

Answer 1

I've managed to fix the problem using some inspiration I've got from how-can-i-clean-up-the-ssisdb
Supposedly the real issue is that cascading deletes don't occur.
I've used below approach to reduce the database size by more than 1GB after the default cleanup procedure.

1: First run the default cleanup procedure

EXEC [internal].[cleanup_server_retention_window]  

2: Check the table sizes

SELECT   
    s.[name] AS [Schema],  
    t.[name] AS [Table],  
    p.[rows] AS [Rows],  
    SUM(a.[total_pages]) * 8 / 1024 AS [TotalMB],   
    SUM(a.[used_pages]) * 8 / 1024 AS [UsedMB],   
    (SUM(a.[total_pages]) - SUM(a.[used_pages])) * 8 / 1024 AS [UnusedMB]  
FROM sys.tables t  
	INNER JOIN sys.schemas s ON s.schema_id = t.schema_id  
	INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id  
	INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id  
	INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id  
WHERE t.[name] NOT LIKE 'dt%'  AND t.[is_ms_shipped] = 0  AND i.[object_id] > 255   
GROUP BY t.[name], s.[name], p.[rows]  
ORDER BY p.[rows] desc, s.[name], t.[name]  

3: Execute manual delete of obsolete operation_id's.
I've shamelessly 'stolen' and then adjusted the code from the stackoverflow post.
First step is creating temp table with a list of all the operation_id's I would like to keep (I've set retention days to 30).
Next delete all the operation_id's that are not within the temp table. (I've checked for all the tables that have a operation_id column and records).

  IF object_id('tempdb..#KEEP_CANDIDATES') IS NOT NULL  
    BEGIN  
        DROP TABLE #KEEP_CANDIDATES;  
    END;  
      
    -- Populate temp table with operation id you like to keep   
    CREATE TABLE #KEEP_CANDIDATES ( operation_id bigint NOT NULL PRIMARY KEY);  
    DECLARE @DaysRetention int = 30;		--Set the retention days  
    INSERT INTO  #KEEP_CANDIDATES (operation_id )  
    SELECT IO.operation_id  
    FROM internal.operations AS IO  
    WHERE IO.start_time >= DATEADD(day, -@DaysRetention, CURRENT_TIMESTAMP);  
  
    -- Delete the records for operation id you dont want to keep    
    DELETE T  
    FROM internal.event_message_context_scaleout AS T  
        LEFT JOIN #KEEP_CANDIDATES AS DC ON DC.operation_id = T.operation_id  
    WHERE DC.operation_id IS NULL;  
      
    DELETE T  
    FROM internal.event_messages_scaleout AS T  
        LEFT JOIN #KEEP_CANDIDATES AS DC ON DC.operation_id = T.operation_id  
    WHERE DC.operation_id IS NULL;  
      
    DELETE T  
    FROM internal.operation_messages_scaleout AS T  
        LEFT JOIN #KEEP_CANDIDATES AS DC ON DC.operation_id = T.operation_id  
    WHERE DC.operation_id IS NULL;  
     
    -- Finally, remove the entry from operations   
    DELETE T  
    FROM internal.operations AS T   
        LEFT JOIN #KEEP_CANDIDATES AS DC ON DC.operation_id = T.operation_id  
    WHERE DC.operation_id IS NULL;  

4: Rebuild indexes to reclaim space

ALTER INDEX ALL ON internal.event_message_context_scaleout REBUILD;  
ALTER INDEX ALL ON internal.event_messages_scaleout REBUILD;  
ALTER INDEX ALL ON internal.operation_messages_scaleout REBUILD;  
ALTER INDEX ALL ON internal.operations REBUILD;  

5: Repeat step 2 and validate results.
Happy times!

Answer 2

Hi @XchangeVisions ,

We can use the following ways to clean SSISDB logs in Azure:

1.Clean up logs with Power Shell

2.Clean up logs with Transact-SQL

Please refer to Clean up SSISDB logs with Azure Elastic Database Jobs.

Best Regards,
Mona

----------

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Hi @XchangeVisions ,

Welcome to Microsoft Q&A Platform. Thanks for posting the query.

As per document, the procedure '[internal].[cleanup_server_log]' is not applicable for Azure SQL Server as 'SINGLE_USER' mode is mandatory to run this procedure and it cannot be achieved in Azure where server is shared and also belongs to availability groups. In addition to alternatives suggested by @Monalv-MSFT , please configure "Default Logging level" appropriately to reduce logging for events and also reduce "Retention Period" to cleanup old logs (Default value is 365 days)

Ref: ssis-catalog-maintenance-in-the-azure-cloud