Problem
I am getting the following error message – “Cannot insert duplicate key row in object ‘dbo.REPLT_100’ with unique index ‘ID_UIDX’. The duplicate key value is (6). (Source: MSSQLServer, Error number: 2601).” The error number in this case is 2601.
This is generated when you try to insert duplicate values into a column or columns with a unique index. How do you resolve this error?
Solution
This is a continuation of the earlier tip, which describes steps to fix transactional replication when it fails due to primary key violations.
In this tip, we will demo a replication failure caused by a duplication in a unique index of a replicated table. Assumptions to follow this tip include familiarity with transactional replication and configuring transactional replication between two databases. Refer to these replication tips on MSSQLTips.com to learn more about replication topics. One excellent article by Robert Pearl focuses on the common data consistency errors faced in transactional replication.
Configure Transactional Replication and Create Tables for Replication
As a first step, ensure transactional replication is configured. Next, use the sample script below to create a few tables in the publisher database for replication.
CREATE TABLE [dbo].[REPLT_100](
[ID] [int] NOT NULL,
[newIDs] [int] NULL,
[ContactPerson] [nvarchar] (20) NULL
PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
In this table, create another unique clustered index using this script.
CREATE UNIQUE NONCLUSTERED INDEX [ID_UIDX] ON [dbo].[REPLT_100]
(
[newIDs] ASC
)ON [PRIMARY]
GO
Add the Article to Transactional Replication
You can add the newly created table to replication. You can refer to this tip, Limit snapshot size when adding new article to SQL Server replication, to do so. Make sure to also create the unique clustered index on the subscriber table.
Once completed, insert a few values into the table using the script below and confirm replication is working as expected.
INSERT INTO [dbo].[REPLT_100]
([ID]
,[newIDs]
,[ContactPerson])
VALUES
(1
,1
,'ddd')
GO
Below is the sample output of this table.
Confirm that the tables are replicating as expected by checking the replication monitor.
Simulate Replication Failure by Forcing Unique Key Violations
In the subscription database, insert a new value in the column containing the unique index. In this case, we can use the script below to simulate the duplication of the unique index.
--Run this in subscription database
INSERT INTO [dbo].[REPLT_100]
([ID]
,[newIDs]
,[ContactPerson])
VALUES
(100
,6
,'mmm')
GO
Below is the output from the subscriber table.
You can see the value of 6 inserted for the newIDs column.
By inserting the same value 6 in the publisher database for the same column, you can break replication. To insert a duplicate entry in the publisher database, you can use the script below.
-- Run this on publisher DB
INSERT INTO [dbo].[REPLT_100]
([ID]
,[newIDs]
,[ContactPerson])
VALUES
(7
,6
,'ddd')
GO
Replication Errors Starts Due to Duplicate Entries
As expected, the replication will fail. You can check the replication status from the replication monitor, as seen in the screenshot below.
As you can see, the error message says “Cannot insert duplicate key row in object 'dbo.REPLT_100' with unique index 'ID_UIDX'. The duplicate key value is (6). (Source: MSSQLServer, Error number: 2601)“. The error number in this case is 2601. This is generated when you try to insert duplicate values into a column or columns with a unique index.
In the earlier primary key violation tip, the error number was 2627. The error message reads as follows: “Violation of PRIMARY KEY constraint 'PK_Sales_OrderLines'. Cannot insert duplicate key in object 'Sales.OrderLines'. The duplicate key value is (231420). (Source: MSSQLServer, Error number: 2627)“.
You can view the details in the distribution database using the script below. We can also filter by the error code to get the details of the replication failure.
Use Distribution
go
select * from dbo.MSrepl_errors
where error_code in ('2601')
You can see that the replication is failing continuously with the error as the duplicate key value of 6 is detected.
Commands and Options to Collect Additional Replication Error Information
As described in the previous tip, you can investigate further using the sp_browsereplcmds command. From the replication monitor, you can use the transaction sequence number to identify additional details of the failed transaction. You can copy the transaction sequence number from the replication monitor as shown.
Replace the @xact_seqno_start and @xact_seqno_end with the transaction sequence number copied from the replication monitor.
Use distribution
go
exec sp_browsereplcmds @xact_seqno_start = '0x0000002800000EC8000400000000',
@xact_seqno_end = '0x0000002800000EC8000400000000',
@publisher_database_id = '1'--Query MSpublisher_databases on distributor DB
You can see that the insert operation is failing for the value ‘6’. Another option to view the error details is to review the distribution agent job directly on the Job Activity Monitor. From the Job Activity Monitor, ensure to click the correct distribution agent job for that database and view the job history.
Right-click on the distribution agent job, review the job steps as shown, and view the job history. You will notice the details of the duplicate entries.
As you can see, the distribution agent job is retrying and failing repeatedly due to the duplicate value.
Checking Replication Latency
You can use the replication monitor to check the latency of undistributed commands. In this demo, we will use a small table with some sample inputs. In a live production database, there may be several transactions, and the latency will start building up due to an issue with a single duplicate entry. You can enter additional rows into the publisher database to check the latency data. You can check this on the replication monitor (see image below).
You can see the number of pending commands and the estimated time remaining. We can also use T-SQL to check this. Run the following script in the distribution database to get this information.
use distribution
go
exec sp_replmonitorsubscriptionpendingcmds
@publisher='DESKTOP-92VB271', -- Name of publisher server
@publisher_db='REPLT', -- Name of publisher DB
@publication='REPLT-Pub', -- Name of publication
@subscriber='DESKTOP-92VB271\INST2', -- Name of subscriber server
@subscriber_db='REPLT', -- Name of subscriber database
@subscription_type='0' -- 0 for push subscription, 1 for pull subscription
The output of this command is below.
Fixing Errors Related to Duplicate Key Values
You may directly delete any duplicate entries in the subscriber table; in this case, as we entered a value of 6 in the subscriber table, you can use the script below.
--Run this on subscriber database
delete from [REPLT].[dbo].[REPLT_100]
where newIDs in(6)
You can refresh the Replication Monitor to check the status or try to stop and start the correct distribution agent job. In a short while, you will see that replication has recovered, and the replication monitor status has also changed.
You can use the command sp_replmonitorsubscriptionpendingcmds mentioned earlier to check for undistributed commands. In this demo, we had to deal with one duplicate entry, but in a real-life scenario, you may have to deal with multiple entries.
Option to Skip Errors in the Distribution Agent Profile
As mentioned in the previous tip, another option is to skip the data consistency errors in the distribution agent. Changing this setting straightaway is generally not recommended as it is important to analyze the reasons and source for the duplicate entries. That way, the issue can be fixed permanently, and you need not worry about missing or skipped transactions. You can refer to the tip described earlier to skip the errors, but while using the custom profile, make sure to use the correct error number. The correct error number for this error related to the duplication in the unique index is 2601. From the replication monitor, make sure to click on the correct subscription to go to the correct distribution agent profile. Once you click on creating the new custom profile, fill in the details shown below.
Enter error number 2601, which is for duplicate entries related to the unique index. Click OK. Then, change the agent profile to use the new custom profile you created.
Using this method, you only skip specific data consistency errors related to the correct error number.
NOTE: For this to work, you must stop and restart the distribution agent job. You can use the script provided in the earlier tip for that purpose. If not, locate the distribution agent job in the job activity monitor to stop and start the job.
-- To STOP the Distribution Agent:
sp_MSstopdistribution_agent @publisher, @publisher_db, @publication, @subscriber, @subscriber_db
--To START the Distribution Agent:
sp_MSstartdistribution_agent @publisher, @publisher_db, @publication, @subscriber, @subscriber_db
If replication still does not work, go to the subscription node as shown. Right-click and select ‘View Synchronization Status’.
Make sure to ‘Stop’ and ‘Start’ as shown below. Based on the number of outstanding transactions to be delivered, it may take time.
Once the synchronization process is complete, check the replication monitor again to check the status of the undistributed commands. Using this option, we were able to get the replication working again.
In this tip, you saw a demo of a known issue in transactional replication due to duplication of the unique key. In the next few tips, we will continue discussing other errors we may encounter in transactional replication.
Next Steps
- Configure transactional replication in your lab server.
- Try out the scripts in this tip for demo purposes.
- Try to break replication in your lab server and simulate the options to fix the errors.
- Review the number of replication tips available on MSSQLTips.com.
