I've run DBCC CHECKDB several times the past couple of months, and it does not indicate any corruption. I'm not sure what column that 5th value 32999 is aligned with. I would say that the error message is citing the wrong index name, but the duplicate value combination doesn't appear to line up with the primary key or unique index either. It should not be unique, and it isn't declared as unique.Īctually, the column values cited in the error don't line up the key columns in the index. However, it appears the index IX_Payment_PurchaseId is intended to query all payments made against a purchase. I'm not on the ETL team, so I can't vouch for the specifics of why the table / indexes were modeled the way they are. I'm reach out here for ideas or common experience before I resort to opening a support case with Microsoft. I havn't resorted to this yet, because I can't intentionally reproduce the issue, and it could be weeks before it occurs again.
DUPLICATE KEY WINDOWS
0 (X64)Įnterprise Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 (Build 9600: ) (Hypervisor)įor a workaround, the following MS support article suggests trace flag 8690 to disable the Spool operation. So, I applied the latest CU4 to SQL Server 2016 SP2 and was optimistic for a week, until it happened again. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90, DATA_COMPRESSION = PAGE) ON ()ĭoing some searching has revealed similar issues in versions 2008 - 2014, and the reccomended solution has been to apply the latest CU.
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90, DATA_COMPRESSION = PAGE) ON ( ),ĬONSTRAINT There is a non-clustered primary key and then a clustered unique index, neither of which are ever cited in the duplicate key error messages. WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100, DATA_COMPRESSION = PAGE) Here is the definition of the index cited in the error:ĬREATE NONCLUSTERED INDEX ON
There are actually a handful of different tables where this key violation issue is occurring, but I'll use PurchasePayment as the example here. I know what you're going to say, but actually there is NOT a unique constraint nor a foreign key constraint on the index IX_Payment_PurchaseId and also no triggers on the table.
UAT and Production actually query from the same source database daily, because the goal is to keep the database model and data between the two environments in tandem, yet the error can occur independently in one environment but not the other. The error occurs seemingly randomly on DEV, UAT, and Production. This error first started occurring several weeks ago during the nightly insert/update process for a data warehouse, and it re-occurs occasionally, maybe once a week or every couple of weeks. There is no apparent correlation between the data and the error. Cannot insert duplicate key row in object 'Payment' with unique index 'IX_Payment_PurchaseId'.
0 kommentar(er)
