Soru SSISDB'yi nasıl temizleyebilirim?


Bunu ayarladığım zaman, saklama süresini göz ardı ettim. Veritabanım oldukça büyük oldu, bu yüzden boyutunu azaltmak istiyorum. Saklama süresini basitçe değiştirirsem (365), paketlerimi çalıştıran SSIS ile ilgili sorunlara neden olur. Küçük artışlarla bile değiştirdim, ancak silme ifadesi yeni işlerin çalışmasını engelleyecek kilitler yaratacaktır.

Bu konuda çalışmak için herhangi bir fikir? Sadece yeni bir SSISDB oluşturmayı düşündüm.


21
2018-02-14 13:58


Menşei




Cevaplar:


Phil Brammer buna ve SSIS kataloğunun bakımı ve beslenmesiyle ilgili bir dizi başka şeye girdi. Katalog Dizin Oluşturma Önerileri.

Kök sorunu

Kök problemi, MS'nin SSI'yi RI ile tasarlamayı denemesi, ancak tembel olmaları ve basamaklı silme işlemlerinin açıkça ele alınmasına karşı olmasına izin vermeleridir.

Kutunun dışında, yeni SSIS 2012 katalog veritabanı (SSISDB), bazı tabloların arasında basamaklı silme yapmak için referans bütünlüğü ayarlanmış bazı temel indeksleme uygulanmıştır.

SQL Agent işini girin, “SSIS Server Bakım İşi.” Bu iş varsayılan olarak gece yarısında çalışacak şekilde ayarlanmış ve işlev görmek için iki katalog parametresi kullanır: “Günlükleri düzenli olarak temizle” ve “Saklama Süresi (günler)”. ayarlandığında, bakım işi belirtilen saklama süresinin dışında kalan verileri temizler.

Bu bakım işi, bir döngüde, bir defada 10 kayıt, dahili işlemlerden ve daha sonra akışaşağında birçok tabloya basamaklar. Bizim durumumuzda, günlük olarak silmek için yaklaşık 3000 işlem kaydımız var (her seferinde 10). Bu da dahili bir süreçten 1,6 milyon satıra dönüşüyor. Bu sadece bir alt masa! Tüm bu süreç tamamen SSISDB veritabanını herhangi bir SELECT / INSERT verisinden tamamen kilitler.

çözüm

MS, işlerin nasıl çalıştığını değiştirene kadar, desteklenen seçenek

Bakım iş programını ortamınız için daha uygun bir zamana taşıyın

Mevcut müşterimde, sadece iş saatlerinde veri yüklüyoruz, böylece iş saatlerinde SSISDB sessiz.

Bakım işini sessiz bir süre boyunca çalıştırmanız bir seçenek değilse, basamaklı silme işlemlerini gerçekleştirmeye çalışmak için kendi silme ifadelerinizi hazırlamayı düşünebilirsiniz. daha az emmek.

Mevcut müşterimde, son 10 ay boyunca her gece yaklaşık 200 paket işletiyoruz ve ayrıca 365 gün tarihimiz var. En büyük tablolarımız, büyüklük sırasına göre.

Schema    Table                   RowCount
internal  event_message_context   1,869,028
internal  operation_messages      1,500,811
internal  event_messages          1,500,803

Bu verilerin hepsinin sürücüsü, internal.operations İçinde sadece 3300 satır var, bu da Phil'in bu verilerin ne kadar katlanarak arttığına dair yorumuna uyuyor.

Yani, tanımla operation_id Temizlenecek ve çekirdeğe geri dönen yaprak tablolarından silinecek internal.operations tablo.

USE SSISDB;
SET NOCOUNT ON;
IF object_id('tempdb..#DELETE_CANDIDATES') IS NOT NULL
BEGIN
    DROP TABLE #DELETE_CANDIDATES;
END;

CREATE TABLE #DELETE_CANDIDATES
(
    operation_id bigint NOT NULL PRIMARY KEY
);

DECLARE @DaysRetention int = 100;
INSERT INTO
    #DELETE_CANDIDATES
(
    operation_id
)
SELECT
    IO.operation_id
FROM
    internal.operations AS IO
WHERE
    IO.start_time < DATEADD(day, -@DaysRetention, CURRENT_TIMESTAMP);

DELETE T
FROM
    internal.event_message_context AS T
    INNER JOIN
        #DELETE_CANDIDATES AS DC
        ON DC.operation_id = T.operation_id;

DELETE T
FROM
    internal.event_messages AS T
    INNER JOIN
        #DELETE_CANDIDATES AS DC
        ON DC.operation_id = T.operation_id;

DELETE T
FROM
    internal.operation_messages AS T
    INNER JOIN
        #DELETE_CANDIDATES AS DC
        ON DC.operation_id = T.operation_id;

-- etc
-- Finally, remove the entry from operations

DELETE T
FROM
    internal.operations AS T
    INNER JOIN
        #DELETE_CANDIDATES AS DC
        ON DC.operation_id = T.operation_id;

Normal uyarılar geçerlidir - İnternette rastgele kodlara güvenme - Tüm bağımlılıkları tanımlamak için ssistalk ve / veya sistem tablolarındaki diyagramları kullanın - Silme işlemlerinizi yalnızca daha küçük işlemlere bölmeniz gerekebilir - Operasyonlar için UR'yi bırakarak fayda sağlayabilirsiniz, ancak kontrol seçeneğiyle yeniden güvende olduklarından emin olun. - Operasyonlar 4 saatten uzun sürerse, dba'nıza danışınız.

Referanslar


29
2018-02-14 14:52



Bu son derece yararlıdır. Veritabanını uçurabilir ve sıfırdan başlayabilir miyim? Tarihi kaybettim ama başka bir düşüş var mı? - Paul Riker
.İspac'in kullanışlı + Ortam öğeleriniz elinizin altında olduğu veya yeniden oluşturulmadığı sürece, bırakma ve yeniden oluşturma işe yarayacaktır. - billinkc
Muhtemelen eklenecek şekilde güncellenmelidir support.microsoft.com/en-us/kb/2829948 - Martin Smith
@MartinSmith Bitti ve teşekkür ederim. Ben yatakta defecated bir 2012 SSISDB temizlemek zorunda gibi BTW zamanlama mükemmel bit - billinkc


Aşağıda bulunan arşivlemeyi yapmak için benzer bir saklı yordam oluşturdum. Herhangi bir hata varsa bana bildirin. Hiçbir garanti vermiyorum, ama benim için iyi çalışıyor. Bu kod hiçbir şekilde cilalı değil ama paylaşmak istedim (yani temp tablosu araçlarının kullanımı sadece bir seferde çalışabilir, belki bir oturum alanı daha iyi olabilir)

MS kendi temiz yordamının LDF dosyasını ouut ve uzun süreler için tabloları kilitler ve bir kez sunucu alanı bitti neden olur bir sorun vardı. Önce daha büyük tabloları silmek ve daha sonra operasyon tablosunu silmek için kendi başıma yazmaya karar verdim. Aşağıdaki bu yordam, günlük alanında 1 GB'den daha fazlasını kullanmaz ve SSIS işleri tüm gün çalışacaksa, bir yakalama 22 olan çok uzun süreler için tabloları kilitlemez.

İlk önce bir tabloya gider

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

ve geçici bir tablo kullanır

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

işte prosedür

    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

0
2018-05-16 00:01