September 19, 2012 at 7:34 am
Hi all,
I have confusion here with replication. I've made some tests and here are results:
I need to troubleshoot environment where cross replication solution is implemented. Don't ask me why, customer wants it.
I made tests on transactional replication, not cross but I don't thing it is different in this case.
I've created replication between two servers on one table with two columns lets say ID column(identity with increment 1) and timestamp column. I've created job, that every ten seconds inserts GETDATE() value into table and this is replicated to another server.
On subscriber table I have manually inserted row with higher ID as as last identity on publisher and have waited for error. when I did not make changes on Replication agent provile, replication failed with error 2627(Violation of PRIMARY KEY constraint 'PK_tab1'. Cannot insert duplicate key in object 'dbo.tab1'.) replication gives error and stopped to work while retrying to insert duplicate key. After this I was able to check transaction details because I see xact_seqno in MSrepl_errors table and commandID so that I could track failed command.
Later I've decided to skip this kind of errors by changing Agent profile, that will omit this kind of errors. But after this change, if some duplicate key issue occurs I see xact_seqno like 0x0000000000000000000000000000 and commandid is also 0 and when i use these result in this script
Use distribution;
Declare @PublisherDB sysname,
@PublisherDBID int,
@SeqNo nchar(22),
@CommandID int
-- Set publisher database name and values from Replication Monitor
Set @PublisherDB = N'testDB'
Set @SeqNo = N'0x0000000000000000000000000000'
Set @CommandID = 0
-- Find the publisher database ID
Select @PublisherDBID = id
From dbo.MSpublisher_databases
Where publisher_db = @PublisherDB
-- Get the command
Exec sp_browsereplcmds
@xact_seqno_start = @SeqNo,
@xact_seqno_end = @SeqNo,
@command_id = @CommandID, I see empty result.
My question is, if this is standard behaviour of SQL server or not, and if yes, is there any way of how to get details of skipped transactions that was causing 2627 code errors.
Thanks for any responses.
Boris.
March 8, 2013 at 2:22 am
Hi... I have the same problem rigth now.
I decided to skip that error, but I would like to know what transaction do that...
Any response will be appreciate!
Any way I'm looking for a solution
Thanks!
March 9, 2016 at 4:01 pm
please refer the below link, i am able to resolve the issue,
http://www.sqlideas.com/2015/05/skip-distributor-agent-error-in-sql.html
--Publisher
USE distribution
GO
DECLARE @PublisherServer VARCHAR(50),
@PublicationDB VARCHAR(50),
@SubscriberServer VARCHAR(50),
@SubscriberDB VARCHAR(50),
@PublicationName VARCHAR(50)
SET @PublisherServer = '<Publisher>'
SET @PublicationDB = 'test'
SET @SubscriberServer = '<Subscriber>'
SET @SubscriberDB = 'test1'
SET @PublicationName = 'testpub'
EXEC Sp_helpsubscriptionerrors
@PublisherServer,
@PublicationDB,
@PublicationName,
@SubscriberServer,
@SubscriberDB
GO
--On Subscriber
USE SubscriberDB
GO
DECLARE @PublisherServer VARCHAR(50),
@PublicationDB VARCHAR(50),
@PublicationName VARCHAR(50)
SET @PublisherServer = '<Publisher>'
SET @PublicationDB = 'test'
SET @PublicationName = 'testpub'
EXEC Sp_setsubscriptionxactseqno
@PublisherServer,
@PublicationDB,
@PublicationName,
0x00000022000000C1000400000000
GO
----------------------------------------------------
Regards,
Syed Jakeer,
----Only SQL Server
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply