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-







Replication Error



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