locked
Transaction Replication Log Reader Error on SQL 2005 SP3 RRS feed

  • Question

  • Hi All

    We have started getting errors on the Log Reader on our Transactional Replication Setup.

    The environment is 3 node cluster with SQL 2005 Enterprise x64 on Windows 2008.

    I have googled/binged but found nothing relating to this error and its possible resolution. Any help is appreciated.

    Error messages:

    The process could not execute 'sp_MSadd_replcmds' on 'SQL2\SQL2'. (Source: MSSQLServer, Error number: 1007)
    Get help: http://help/1007

    Cannot insert duplicate key row in object 'dbo.MSrepl_transactions' with unique index 'ucMSrepl_transactions'. (Source: MSSQLServer, Error number: 1007)
    Get help: http://help/1007

    Cannot insert duplicate key row in object 'dbo.MSrepl_transactions' with unique index 'ucMSrepl_transactions'.Cannot insert duplicate key row in object 'dbo.MSrepl_commands' with unique index 'ucMSrepl_commands'. (Source: MSSQLServer, Error number: 1007)
    Get help: http://help/1007

    Cannot insert duplicate key row in object 'dbo.MSrepl_transactions' with unique index 'ucMSrepl_transactions'.Cannot insert duplicate key row in object 'dbo.MSrepl_commands' with unique index 'ucMSrepl_commands'.Cannot insert duplicate key row in object 'dbo.MSrepl_transactions' with unique index 'ucMSrepl_transactions'. (Source: MSSQLServer, Error number: 1007)
    Get help: http://help/1007

    Cannot insert duplicate key row in object 'dbo.MSrepl_transactions' with unique index 'ucMSrepl_transactions'.Cannot insert duplicate key row in object 'dbo.MSrepl_commands' with unique index 'ucMSrepl_commands'.Cannot insert duplicate key row in object 'dbo.MSrepl_transactions' with unique index 'ucMSrepl_transactions'.Cannot insert duplicate key row in object 'dbo.MSrepl_commands' with unique index 'ucMSrepl_commands'. (Source: MSSQLServer, Error number: 1007)
    Get help: http://help/1007

    Cannot insert duplicate key row in object 'dbo.MSrepl_transactions' with unique index 'ucMSrepl_transactions'.Cannot insert duplicate key row in object 'dbo.MSrepl_commands' with unique index 'ucMSrepl_commands'.Cannot insert duplicate key row in object 'dbo.MSrepl_transactions' with unique index 'ucMSrepl_transactions'.Cannot insert duplicate key row in object 'dbo.MSrepl_commands' with unique index 'ucMSrepl_commands'.Cannot insert duplicate key row in object 'dbo.MSrepl_transactions' with unique index 'ucMSrepl_transactions'. (Source: MSSQLServer, Error number: 1007)
    Get help: http://help/1007

    Batches were not committed to the Distributor. (Source: MSSQL_REPL, Error number: MSSQL_REPL22020)
    Get help: http://help/MSSQL_REPL22020

    The last step did not log any message! (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)

    Wednesday, July 21, 2010 9:53 AM

All replies

  • select UndelivCmdsInDistDB, DelivCmdsInDistDB, msa.publisher_db, publication,
          article,destination_object,source_owner,source_object,comments,start_time,[time],duration,
          delivered_transactions,total_delivered_commands,delivery_rate,updateable_row,xact_seqno
          from msdistribution_status mss
          inner join MSarticles msa
          on mss.article_id = msa.article_id
          inner join MSdistribution_history msd
          on msd.agent_id = mss.agent_id
          inner join MSpublications msp on
          msp.publication_id = msa.publication_id

    from above you will find the xact_seqno

    sp_browsereplcmds @xact_seqno_start =''

    or


    sp_browsereplcmds [ [ @xact_seqno_start = ] 'xact_seqno_start' ]
        [ , [ @xact_seqno_end = ] 'xact_seqno_end' ]
        [ , [ @originator_id = ] 'originator_id' ]
        [ , [ @publisher_database_id = ] 'publisher_database_id' ]
        [ , [ @article_id = ] 'article_id' ]
        [ , [ @command_id= ] command_id ]
        [ , [ @agent_id = ] agent_id ]
        [ , [ @compatibility_level = ] compatibility_level ]

    Monday, May 30, 2011 3:21 PM
  • Try sp_replrestart

    You will then need to run a validation to ensure that everything is consistent. Tables which fail validation will need to be re-synchronized.


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Tuesday, May 31, 2011 1:35 PM
    Moderator