Monday 8 May 2017

Transaction Replication – ERROR- Violation of PRIMARY KEY constraint and some other errors


The common data consistency errors that can occur are:
  • 2601 Cannot insert duplicate key row in object '%.*ls' with unique index '%.*ls'
  • 20598 The row was not found at the Subscriber when applying the replicated command.
  • 2627 Violation of PRIMARY KEY constraint 'PK__A'. Cannot insert duplicate key in object 'dbo.A'.

The below steps has complete example to fix PRIMARY KEY violation issue in Replication -

You will get below error in Replication Monitor window -->  Distributor to subscriber history tab. Also check Undistributed command in Replication monitor.

Error-

Command attempted:
if @@trancount > 0 rollback tran
(Transaction sequence number: 0x00000024000002F0000400000000, Command ID: 1)
Error messages:
Violation of PRIMARY KEY constraint ‘PK__t1__3213E83FBA2CFEA2’. Cannot insert duplicate key in object ‘dbo.t1’. The duplicate key value is (3). (Source: MSSQLServer, Error number: 2627)
Get help: http://help/2627
Violation of PRIMARY KEY constraint ‘PK__t1__3213E83FBA2CFEA2’. Cannot insert duplicate key in object ‘dbo.t1’. The duplicate key value is (3). (Source: MSSQLServer, Error number: 2627)
Get help: http://help/2627

Using below command also we can get error details-

Use Distribution
go

select * from dbo.MSrepl_errors where error_code in ('2601','2627','25098')


Now Copy "Sequence Number" form the error and use in below query. 


SELECT  art.publisher_id ,
        art.publisher_db ,
        art.publication_id ,
        art.article ,
        art.article_id  ,
        art.destination_object  ,
        art.source_owner  ,
        art.source_object
FROM    distribution.dbo.MSarticles AS art
        JOIN distribution.dbo.MSrepl_commands AS com
        ON art.Article_id = com.Article_id
WHERE   com.xact_seqno = 0x00000024000002F0000400000000


The above query will return publisher_database_id and article_id.
which we will use in below query-


EXEC distribution.dbo.sp_browsereplcmds
     @xact_seqno_start = '0x00000024000002F0000400000000' ,
     @xact_seqno_end = '0x00000024000002F0000400000000' ,
     @publisher_database_id = 1 ,
     @article_id = 1 ,
     @command_id = 1

It above query will return Command value, this is the duplicate value which has entered in the table of subscriber end.

Go to the subscriber the delete the duplicate record.

After sometime the undistributed command value will be Zero.
That means records has been processed. Also verify in error in
Distributor to subscriber history Tab.


Another method-

We can avoid/ignore Primary Key violation error and other error
and continue the transactions.

To know this please visit link-





Some other error which may occur in Replication- 


Use Distribution
go


select * from dbo.MSrepl_errors 


error_code
error_text
121
Communication link failure
18456
Login failed for user 'REDD\XITSS12'.
20052
The process could not access database 'RAM' on server 'CO1BCAPT01'.
4060
Cannot open database "MYDB" requested by the login. The login failed.
53
Named Pipes Provider: Could not open a connection to SQL Server [53].
53
A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
HYT00
Login timeout expired

if @@trancount > 0 rollback tran




No comments:

Post a Comment