Transaction Replication - Duplicate Key Inserted Error
January 23, 2001
If you've set up replication without immediately updating (or even queued updating in SQL2K) subscribers, you may see this error if you allow write access to tables on the subscriber. If a user inserts a record into the subscriber, then later that same key is generated on the publisher, this error will result. Not granting insert permissions on the subscriber is a great way to stop this ---- if your situation allows!
When it happens, the distribution agent will shutdown. The usual way to fix this is to reinitialize the subscription and push a new snapshot to the subscriber. If you're using SQL 7, SQL takes share locks on tables during generation of the snapshot which will block your users. You also have to look at how long it will take to get the new snapshot to the subscriber (db size and bandwidth).
However, there are alternatives. One is to add the -skiperrors parameter to the distribution agent and restart it. Not a great solution, but it will work. You can either leave it in place or remove it once you've made it past the "bad" record. Depending on how the error occurred, this may leave you with incorrect data on the subscriber. You can then join to the subscriber via linked server to update the record to match the one on the publisher. Or just use this as an interim fix and do the snapshot after hours.
Another alternative is to remove the undistributed record from the distribution database. Basically you need to identify the transaction and delete it from the MSRepl_Command table. One way to find is to use sp_browsereplcmds - keep in mind that the publisher_database_id is NOT the database ID - you need to look it up in MSPublishers_Databases to get the actual database name. You're looking for the earliest insert transaction for the database in question. On my test machine this returned:
So in this case the transaction we need to remove is xact_seqno 0x000000110000014E0004. If you'll take a look at the error detail in the first image above, you'll notice that the last command matches the command in the output from sp_browsereplcmds, confirming our research. Now you just need to execute:
Restart the distribution agent. Your subscriber still doesn't necessarily have the same data for that one row as the publisher, so you can update the subscriber or redo the snapshot as discussed earlier.
The last alternative is figure out the duplicate key and remove it from the subscriber. Looking at the text of the last command from the error detail, you can see the values that are being passed to the sp_MSins_Books stored procedure on the subscriber. A quick look at that sp on the subscriber confirms that the first parameter is the primary key, so all we need to do is connect to the subscriber and delete the record:
Then restart the distribution agent. This method keeps your subscriber in sync without having to make any other changes. And of the three, I believe it represents the least risk of error.
For more information about the -skiperror parameter see BOL or MSDN.