Change Tracking causes errors to appear 'Cannot insert duplicate key row in object'

Question

Hi,

I am using SQL 2019 Enterprise Version. I have a simple process that runs with no errors updating / merging between tables in the same database. If i enable change tracking on one of the destination tables the process starts to fail with the error Cannot insert duplicate key row in object ' XXXXX ' with unique index '. As soon as i disable the change tracking everything works fine again. There are no errors in the log.

Anyone else experienced this issue, or know how to resolve the problem?

Thank you!

Answer 1

It certainly sounds like a bug in the product.

I'm afraid that we cannot do much here. Well, we could ask you to post a repro that demonstrates the issue, but that could take you a bit of time. And in the end we might only say "yes, we can repro this", but unless there is something that stands out as special, I don't think that we will be able to help you to get around the problem.

So I would advice you that you first check "SELECT @@version" and verify that you are on CU11 of SQL 2019. If you are not, apply CU11, which is the most recent CU for SQL 2019, to see if that it helps.

If it does not, open a support case, if this is a blocking issue for you. (And I guess it is.)

Well, an alternative is of course to replace the MERGE with individual INSERT and UPDATE statements, and there are blog posts out there that gives MERGE bad press. But I find MERGE quite nifty myself, so I am not keen on that workaround.

Answer 2

Just to add an update, we removed all the merge statements and are just using updates and inserts, and still seeing the same message. We also upgraded to the latest cu12 and still seeing the same issue.

I'm reaching out to Ms today hopefully to get some assistance on it.