OK, here I will restate the problem, this time with less obfuscation, except for the actual data values involved.
Here is a sample error.
Cannot insert duplicate key row in object 'storeops.Payment_AgreementPayment'
with unique index 'IX_storeops_Payment_AgreementPayment_AgreementId'.
The duplicate key value is
, 219999 ).
Here is the definition for the index cited in the sample error message above. Notice the index is not unique, and notice there are (5) columns cited in the error message but only (4) key columns in the index.
CREATE NONCLUSTERED INDEX [IX_storeops_Payment_AgreementPayment_AgreementId]
) 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)
The error occurs only occasionally during the nightly ETL run, maybe once every two weeks, at seemingly random times, on a handful of similar tables and. The error may occur in DEV, UAT, and Production, one environment successfully processing a dataset that fails in another environment. Subsequently rerunning a failed run will result in success.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho