Post reply

blocking in distribution cleanup

  • October 27, 2004 at 12:43 pm

    #86777

    about 2 hours ago I started having some problems with my transactional replication.  The log reader agent is getting blocked by the distribution agent, with the former trying to INSERT in MSrepl_transactions:

    INSERT

    INTO MSrepl_transactions

    VALUES (@publisher_database_id,

    substring( @tempdata, 1, 10 ), @seqno, @date)

     

    and the latter trying to DELETE from it:

    delete MSrepl_transactions WITH (PAGLOCK) from MSrepl_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

    -- use nolock to avoid deadlock

    not exists (select * from MSrepl_commands c (nolock) where

    c.publisher_database_id = @publisher_database_id and

    c.xact_seqno = MSrepl_transactions.xact_seqno)

     

    Any ideas of what I can do to avoid this?  It was working fine until this morning.

    Thanks

     

  • October 27, 2004 at 5:35 pm

    #528233

    I don't know why this is happening but what you could do in the short term is stop the Distribution Job and let the Log Reader do its thing and then enable the Distribution job again. This should fix it in the short term.

    Kindest Regards,

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply