Description

In some cases, DMS may fail to use the best approach for data insertion. The data will still be inserted correctly into the database, but you may see the following types of failures reported:

1. The SQL server log files contain the following exception details:
   

   Exception Error: 2627, Severity: 14, State: 1 .Net SqlClient Data Provider sitecore 4132 99 2013-12-15 15:57:18.200 SQL:BatchCompleted insert bulk…

2. The Sitecore log files contain the following sequence of log entries:
   

   ManagedPoolThread #0 2013-12-15 15:57:18 DEBUG Trying bulk update...
   ManagedPoolThread #0 2013-12-15 15:57:18 DEBUG Trying 1 by 1 update...

3. If you attach Visual Studio to the Sitecore process and configure it to break on ‘First chance exception of type sqlexception’, you will see the following message:
   

   Violation of PRIMARY KEY constraint 'PK_*'. Cannot insert duplicate key in object 'dbo.*'. The duplicate key value is…

Solution

Sitecore Analytics uses a bulk insert method as one of the fastest data insert techniques. This method allows to insert a large batch of collected data in the most optimal way. However, such bulk update operations might fail in certain situations.

If a batch of information cannot be inserted using the bulk insert method, Sitecore will catch the related exception and handle it appropriately by performing the update using less optimal techniques (1-by-1 update).

So even if such failures are reported in the log files, all the submitted data has been inserted into the database anyway.