Comment puis-je nettoyer la SSISDB?

j'ai créé une procédure stockée similaire pour faire l'archivage qui est ci-dessous. Laissez-moi savoir si tout les bugs. Je n'offre aucune garantie, mais ça marche très bien pour moi. Ce code n'est en aucun cas poli, mais je voulais le partager (c'est-à-dire que l'utilisation de la table temp ne peut être exécutée qu'une fois à la fois, peut-être qu'une table scoped de session serait mieux)

j'ai eu un problème où par la procédure propre DE MS soufflait sur le fichier LDF et verrouillait les tables pendant de longues périodes, et une fois causé le serveur à exécuter de l'espace. J'ai décidé d'écrire mon propre pour supprimer les grandes tables en premier et ensuite supprimer la table d'opération. Cette procédure ci-dessous n'utilise jamais plus de 1 GO dans l'espace log, et ne verrouille pas les tables pendant de très longues périodes, ce qui est une capture 22 lorsque les tâches SSIS doivent fonctionner toute la journée.

d'Abord il se connecte à une table

CREATE TABLE [dbo].[ETL_SSIS_Operations_Archived](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [operation_id_str] [varchar](900) NOT NULL,
    [event_messages_context] [int] NULL,
    [event_messages] [int] NULL,
    [operation_messages] [int] NULL,
    [num_operators] [int] NULL,
    [chunksize] [int] NULL,
    [DateStarted] [datetime] NOT NULL,
    [DateFinished] [datetime] NULL,
    [executionSecs] [int] NULL,
    [DelOperationsDateStarted] [datetime] NULL,
    [DelOperationsDateFinished] [datetime] NULL,
    [DelOperationsExecutionSecs] [int] NULL
) ON [PRIMARY]
GO

et utilise une table temporaire

CREATE TABLE [dbo].[tmp_etl_operations_id](
    [operation_id] [int] NULL,
    [dateCreated] [datetime] NULL default getdate()
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tmp_etl_operations_id] ADD  DEFAULT (getdate()) FOR [dateCreated]
GO

voici la procédure

    CREATE PROCEDURE [dbo].[sp_Archive_SSIDB_Catalogue]
AS
BEGIN

    DECLARE @MyCursor as CURSOR;
    DECLARE @l_operation_id int;
    declare @l_rows_del int = 1

    declare @l_operation_id_str varchar(8000) = ''
    declare @l_id int

    declare @l_event_message_context int = 0
    declare @l_event_messages        int = 0
    declare @l_operation_messages    int = 0

    declare @l_loop_num int = 1
    declare @C_BULK_NUM int = 100
    declare @C_CHUNK_SIZE int = 100000

    declare @l_last_rec char(1)

    SET @MyCursor = CURSOR FOR
       with params as
       (
           -- i round up the midnight that day, just so i know once it is done for the day it is done
           -- and if the real maintenance job was to run after this (just for the sake of it to double ensure nothing has been missed), but not actually need to do
           -- anything as its already done in here, no new operations would have snuck in due to the sliding system time
           SELECT cast(dateadd(day,1,GETDATE() - CONVERT(int,property_value)) as date)  ArchiveDate 
           FROM  ssisdb.[catalog].[catalog_properties]
           WHERE property_name = 'RETENTION_WINDOW'
       )
       select operation_id,iif(r=c,'Y','N') lastrec
       from
       (
           select operation_id,row_number() over (partition by null order by operation_id) r,count(*) over (partition by null) c
           FROM ssisdb.[internal].[operations] 
           WHERE ( [end_time] <= (select ArchiveDate from params)
           -- A special case when END_TIME is null, we will delete the records based on the created time 
           OR ([end_time] IS NULL AND [status] = 1 AND [created_time] <= (select ArchiveDate from params) ))
       ) x
       order by operation_id


    OPEN @MyCursor;
    FETCH NEXT FROM @MyCursor INTO @l_operation_id,@l_last_rec

    WHILE @@FETCH_STATUS = 0
    BEGIN
        set @l_operation_id_str = @l_operation_id_str+','+cast(@l_operation_id as varchar(100))

        if @l_loop_num = 1
        begin
           delete from tmp_etl_operations_id
           set @l_operation_id_str = cast(@l_operation_id as varchar(100))
        end

        insert into tmp_etl_operations_id (operation_id)  select @l_operation_id

        if @l_loop_num = @C_BULK_NUM or @l_last_rec='Y'
        begin
            set @l_loop_num = 1

            set @l_event_message_context = 0
            set @l_event_messages        = 0
            set @l_operation_messages    = 0

            insert into ETL_SSIS_Operations_Archived ([operation_id_str], num_operators,chunksize, event_messages_context, event_messages, operation_messages, datestarted)
            select @l_operation_id_str, @C_BULK_NUM,@C_CHUNK_SIZE,@l_event_message_context,@l_event_messages,@l_operation_messages,getdate()
            --where 0 = (select count(*) from ETL_SSIS_Operations_Archived where operation_id=@l_operation_id_str)

            set @l_id = Scope_Identity() 

            set @l_rows_del = @C_CHUNK_SIZE
            while (@l_rows_del >= @C_CHUNK_SIZE)
            begin
             delete top (@C_CHUNK_SIZE)
             from   ssisdb.internal.event_message_context
             where  operation_id in (select operation_id from etl..tmp_etl_operations_id)

             set @l_rows_del = @@ROWCOUNT
             set @l_event_message_context = @l_event_message_context+@l_rows_del

             update ETL_SSIS_Operations_Archived 
             set    event_messages_context = event_messages_context+@l_rows_del
             where  id = @l_id--operation_id = @l_operation_id_str

            end

            set @l_rows_del = @C_CHUNK_SIZE

            while (@l_rows_del >= @C_CHUNK_SIZE)
            begin
             delete top (@C_CHUNK_SIZE)
             from   ssisdb.internal.event_messages
             where  operation_id in (select operation_id from tmp_etl_operations_id)

             set @l_rows_del = @@ROWCOUNT
             set @l_event_messages = @l_event_messages+@l_rows_del

             update ETL_SSIS_Operations_Archived 
             set    event_messages = event_messages+@l_rows_del
             where  id = @l_id--operation_id = @l_operation_id_strwhere  operation_id = @l_operation_id_str 

            end

            set @l_rows_del = @C_CHUNK_SIZE
            while (@l_rows_del >= @C_CHUNK_SIZE)
            begin
             delete top (@C_CHUNK_SIZE)
             from   ssisdb.internal.operation_messages
             where  operation_id in (select operation_id from tmp_etl_operations_id)

             set @l_rows_del = @@ROWCOUNT
             set @l_operation_messages = @l_operation_messages+@l_rows_del

             update ETL_SSIS_Operations_Archived 
             set    operation_messages = operation_messages+@l_rows_del
             where  id = @l_id--operation_id = @l_operation_id_strwhere  operation_id = @l_operation_id_str -- 

            end

             update ETL_SSIS_Operations_Archived 
             set    DateFinished = getdate()
                   ,executionSecs =  Datediff(s, DateStarted, getdate())
                   ,DelOperationsDateStarted = getdate()
             where  id = @l_id--operation_id = @l_operation_id_strwhere  operation_id = @l_operation_id_str -- 


             -- lets delete the operations now
             delete --top (@C_CHUNK_SIZE)
             from   ssisdb.internal.operations
             where  operation_id in (select operation_id from tmp_etl_operations_id)

             update ETL_SSIS_Operations_Archived 
             set    DelOperationsDateFinished = getdate()
                   ,DelOperationsExecutionSecs =  Datediff(s, DelOperationsDateStarted, getdate())
             where  id = @l_id--operation_id = @l_operation_id_strwhere  operation_id = @l_operation_id_str -- 

        end
        else
        begin
            set @l_loop_num = @l_loop_num+1
        end

        FETCH NEXT FROM @MyCursor INTO @l_operation_id,@l_last_rec


    END

    CLOSE @MyCursor;
    DEALLOCATE @MyCursor;

END