设置Distribution clean up 每次删除Command的数量

    WHILE 1 = 1
    BEGIN
        DELETE TOP(20000) MSrepl_commands WITH (PAGLOCK) from MSrepl_commands with (INDEX(ucMSrepl_commands))
            WHERE publisher_database_id = @publisher_database_id 
                AND xact_seqno IN (SELECT DISTINCT snap_xact_seqno 
                                    FROM @snapshot_xact_seqno)
            OPTION (MAXDOP 1)

        SELECT @row_count = @@rowcount

        -- Update output parameter
        SELECT @num_commands = @num_commands + @row_count
    
        IF @row_count < 20000 -- passed the result set.  We're done
            BREAK
    END

WHILE 1 = 1
    BEGIN
        if @has_immediate_sync = 0
            DELETE TOP(20000) 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(20000) 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 < 20000 -- passed the result set.  We're done
            BREAK
    END

USE distribution
GO

SELECT
    T.[publisher_database_id],
    DATEPART(mm, [entry_time]) 'month',
    DATEPART(dd, [entry_time]) 'day',
    DATEPART(hh, [entry_time]) 'hour',
    COUNT(C.[xact_seqno]) 'count of commands'
FROM [dbo].[MSrepl_transactions](nolock) T
INNER JOIN [dbo].[MSrepl_commands](nolock) C
    ON T.[xact_seqno] = C.[xact_seqno]
        and T.publisher_database_id=c.publisher_database_id
GROUP BY    T.[publisher_database_id],
            DATEPART(mm, [entry_time]),
            DATEPART(dd, [entry_time]),
            DATEPART(hh, [entry_time])
ORDER BY 1, 2, 3, 4

USE distribution
GO

SELECT
    T.[publisher_database_id],
    DATEPART(mm, [entry_time]) 'month',
    DATEPART(dd, [entry_time]) 'day',
    DATEPART(hh, [entry_time]) 'hour',
    COUNT(C.[xact_seqno]) 'count of commands'
FROM [dbo].[MSrepl_transactions](nolock) T
INNER JOIN [dbo].[MSrepl_commands](nolock) C
    ON T.[xact_seqno] = C.[xact_seqno]
GROUP BY    T.[publisher_database_id],
            DATEPART(mm, [entry_time]),
            DATEPART(dd, [entry_time]),
            DATEPART(hh, [entry_time])
ORDER BY 1, 2, 3, 4