Cannot insert duplicate key row in object 'Payment' with unique index 'IX_Payment_PurchaseId'. The duplicate key value is (2999, C1234, 2018-12-13, 2018-12-14, 32999).
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. The error occurs seemingly randomly on DEV, UAT, and Production. 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.
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. There are actually a handful of different tables where this key violation issue is occurring, but I'll use PurchasePayment as the example here.
Here is the definition of the index cited in the error:
CREATE NONCLUSTERED INDEX [IX_Payment_PurchaseId] ON [PurchasePayment]
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)
ON [ps_RowEndDate] ( [RowEndDate] )
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.
PRIMARY KEY NONCLUSTERED
)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 [ps_RowEndDate]( [RowEndDate] ),
)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 [ps_RowEndDate]([RowEndDate])
) ON [ps_RowEndDate]([RowEndDate])
Doing some searching has revealed similar issues in versions 2008 - 2014, and the reccomended solution has been to apply the latest CU.
So, I applied the latest CU4 to SQL Server 2016 SP2 and was optimistic for a week, until it happened again. So, the CU has not resolved the issue.
Microsoft SQL Server 2016 (SP2-CU4) (KB4464106) - 13.0.5233.0 (X64)
Nov 3 2018 00:01:54
Enterprise Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)
For a workaround, the following MS support article suggests trace flag 8690 to disable the Spool operation. I havn't resorted to this yet, because I can't intentionally reproduce the issue, and it could be weeks before it occurs again.
I'm reach out here for ideas or common experience before I resort to opening a support case with Microsoft.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho