針對 Distribution 系統資料庫的 "清除散發: distribution" 作業停止,導致 Distrubution 過大

FB 上有朋友問:

How to resolve when SQL Distribution Database is growing huge (+80g)

檢查DB 有二個Table data 很大
Table MSrepl_commands 已經有 277440950 筆
Table MSrepl_transactions    已經有 80854199  筆

在散發 sql server有個 JOB 清除散發: distribution(Distribution clean up: distribution) 會 CLEAR Distribution Database,可是被關了。如果把job start,replication job 會執行很久,好像卡住。如何補救?

 

我建議的測試如下,但不知是否解了他的問題

declare @i int=365*24,@j int  –若保留了一年,一年之內批次慢慢刪,若不只一年,這個值再加大
while @i>73  — 預設是保留三天
begin
    set @j=@i-24
    EXEC dbo.sp_MSdistribution_cleanup @min_distretention = @j, @max_distretention = @i
    print @i/24
    waitfor delay ’00:00:10′ –留空給系統 run 其他的 replication
    set @i-=24
end

 

數日後,後續…

FB 友:

dbo.sp_MSdistribution_cleanup job 是在2013/6/9 就未執行,817*24=19608 H

–我執行以下command 時 EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 19607, @max_distretention = 19608–只執行clear up 1 h 時間data .

用 SELECT * FROM master..sysprocesses where blocked>0 檢查發現有許多 replication spid 被blocked. 完成無法執行.

請問老師可有其他方法?

 

我:

是否可以用 Job 排程,在較不忙的時候啟動,並記下最後執行到的時間,下次到排程時再啟動

接續清接下來的時程

 

FB 友:

MSrepl_commands table 可以用手動直接delete ? 有什麼方法?

因為這db 是24 h work , 沒有空閒時間.

 

我:

直接刪系統資料資料表,我怕會亂掉

alter database distribution set read_committed_snapshot on

這或許可以解 Lock 很嚴重的狀況…

 

FB 友:

另外問題,如何得知MSrepl_commands 已經多少沒清除了.

set read_committed_snapshot on

如果完成sp_MSdistribution_cleanup

需要SET 回read_committed_snapshot OFF 嗎?

 

我:

這會耗 tempdb,且我不知道有沒有其他的影響

MSrepl_commands 已經多少沒清除了,這我不確定,看起來真正刪除資料的是這個 sp_MSdelete_publisherdb_trans 預存程序內的以下語法:

    WHILE 1 = 1
    BEGIN
        if @has_immediate_sync = 0
            DELETE TOP(2000) MSrepl_commands WITH (PAGLOCK) from MSrepl_commands with (INDEX(ucMSrepl_commands)) where
                publisher_database_id = @publisher_database_id and
                xact_seqno <= @max_xact_seqno and
                (type & ~@snapshot_bit) not in (@directory_type, @alt_directory_type) and
                (type & ~@replpost_bit) <> @scriptexec_type
                OPTION (MAXDOP 1)
        else
            — Use nolock hint on subscription table to avoid deadlock
            — with snapshot agent.
            DELETE TOP(2000) MSrepl_commands WITH (PAGLOCK) from MSrepl_commands with (INDEX(ucMSrepl_commands)) where
                publisher_database_id = @publisher_database_id and
                xact_seqno <= @max_xact_seqno and
                — do not delete directory, alt directory or script exec commands. they are deleted
                — above. We have to do this because we use a (nolock) hint and we have to make sure we
                — don’t delete dir commands when the file has not been cleaned up in the code above. It’s
                — ok to delete snap commands that are out of retention and perform lazy delete of dir
                (type & ~@snapshot_bit) not in (@directory_type, @alt_directory_type) and
                (type & ~@replpost_bit) <> @scriptexec_type and
                (
                    — Select the row if it is older than max retention.
                    xact_seqno <= @max_immediate_sync_seqno or
                    — Select the snap cmd if it is not for immediate_sync article
                    — We know the command is for immediate_sync publication if
                    — the snapshot tran include articles that has virtual
                    — subscritptions. (use subscritpion table to avoid join with
                    — article and publication table). We skip sync tokens because
                    — they are never pointed to by subscriptions…
                    (
                        (type & @snapshot_bit) <> 0 and
                        (type & ~@snapshot_bit) not in (@syncinit, @syncdone) and
                        not exists (select * from MSsubscriptions s with (nolock) where
                            s.publisher_database_id = @publisher_database_id and
                            s.article_id = MSrepl_commands.article_id and
                            s.subscriber_id < 0)
                    )
                )
                OPTION (MAXDOP 1)

        select @row_count = @@rowcount
        — Update output parameter
        select @num_commands = @num_commands + @row_count
   
        IF @row_count < 2000 — passed the result set.  We’re done
            BREAK
    END

以及它所呼叫的 sp_MSdelete_dodelete

CREATE PROCEDURE sp_MSdelete_dodelete
    @publisher_database_id int,
    @max_xact_seqno varbinary(16),
    @last_xact_seqno varbinary(16),
    @last_log_xact_seqno varbinary(16),
    @has_immediate_sync bit = 1
WITH RECOMPILE
as
begin
        declare @second_largest_log_xact_seqno varbinary(16)
        set @second_largest_log_xact_seqno = 0x0

        if @last_log_xact_seqno is not NULL
        begin
            –get the second largest xact_seqno among log entries
            select @second_largest_log_xact_seqno = max(xact_seqno)
            from MSrepl_transactions
            where publisher_database_id = @publisher_database_id
                and xact_id <> 0x0
                and xact_seqno < @last_log_xact_seqno

            if @second_largest_log_xact_seqno is NULL or substring(@second_largest_log_xact_seqno, 1, 10) <> substring(@last_log_xact_seqno, 1, 10)
            begin
                set @second_largest_log_xact_seqno = 0x0
            end
        end

       
        if @has_immediate_sync = 0
            delete TOP(5000) MSrepl_transactions WITH (PAGLOCK) from MSrepl_transactions with (INDEX(ucMSrepl_transactions)) where
                publisher_database_id = @publisher_database_id and
                xact_seqno <= @max_xact_seqno and
                xact_seqno <> @last_xact_seqno and
                xact_seqno <> @last_log_xact_seqno and
                xact_seqno <> @second_largest_log_xact_seqno –ensure at least two log entries are left, when there existed more than two log entries
                OPTION (MAXDOP 1)
        else
            delete TOP(5000) MSrepl_transactions WITH (PAGLOCK) from MSrepl_transactions with (INDEX(ucMSrepl_transactions)) where
                publisher_database_id = @publisher_database_id and
                xact_seqno <= @max_xact_seqno and
                xact_seqno <> @last_xact_seqno and
                xact_seqno <> @last_log_xact_seqno and 
                xact_seqno <> @second_largest_log_xact_seqno and –ensure at least two log entries are left, when there existed more than two log entries
                — use nolock to avoid deadlock
                not exists (select * from MSrepl_commands c with (nolock) where
                    c.publisher_database_id = @publisher_database_id and
                    c.xact_seqno = MSrepl_transactions.xact_seqno and
                    c.xact_seqno <= @max_xact_seqno)
            OPTION (MAXDOP 1)
end

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 變更 )

Twitter picture

You are commenting using your Twitter account. Log Out / 變更 )

Facebook照片

You are commenting using your Facebook account. Log Out / 變更 )

Google+ photo

You are commenting using your Google+ account. Log Out / 變更 )

連結到 %s

%d 位部落客按了讚: