Post reply

Primary Key Violation

  • July 16, 2015 at 12:18 am

    #309936

    Hi ,

    in my transactional replication my replication job failed with error of Primary Key Violation.

    what is steps need to resolve the issue.

  • July 16, 2015 at 12:42 am

    #1812847

    did someone do Insert on replicated table on subscriber? i would say reinitialize subscription.

  • July 16, 2015 at 12:55 am

    #1812849

    is there any options to do this

  • July 16, 2015 at 6:34 am

    #1812918

    Have you got the transaction sequence number?

    You can browse SQL RELP CMDS and see what the row is that is trying to be inserted and either delete it from the subscriber, or remove it from the distributor.

    This means your replication design may not be servicing the business requirement correctly though and new rules put in place to prevent this in future... but you should be able to repair it without re-inializing.

    Try the following commands...

    use distribution

    go

    sp_browsereplcmds (goggle usage)

    -- get insert comand

    select * from MSrepl_commands

    where command_id = ?

    and article_id = ?

    and xact_seqno = ?

    -- remove command from the transaction list

    select *

    FROM MSrepl_transactions

    WHERE xact_seqno IN (SELECT xact_seqno

    FROM MSrepl_commands

    WHERE article_id = ?--Insert your Article_ID here

    --AND publisher_database_id IN () --Use if you have Multiple Publishers connected to One Distributor

    AND xact_seqno IN

    (

    ?

    )

    AND command_id = ? -- Use this if you would like to only remove specific command id

    )

    Good luck

  • July 16, 2015 at 7:15 am

    #1812930

    I have an example of how to locate the transaction causing the problem here - http://sqlrambling.net/2014/10/31/replication-monitor-basic-overview/

    Steve Hall
    Linkedin
    Blog Site

Viewing 5 posts - 1 through 4 (of 4 total)

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