Description

There might be an increased resource consumption and increased response time on xDB Processing Pools database because of the infinite loop inside stored procedures. It might affect the following stored procedures of the [Processing.Pools] database:

[xdb_processing_pools].[GenericProcessing_CheckoutBatch]
[xdb_processing_pools].[ContactProcessing_CheckoutBatch]
[xdb_processing_pools].[InteractionHistoryProcessing_CheckoutBatch]
[xdb_processing_pools].[InteractionLiveProcessing_CheckoutBatch]

All of them contain the following part:

WHILE( @Retry = 1 )
BEGIN
...
[Attempts] = ([Attempts] + 1),
...
END

where the [Attempts] field is of [smallint] type, thus limited to maximum 32767. If for any reason the field reaches its maximum, an "Arithmetic overflow" error occurs and the loop becomes infinite.

Solution

To resolve the issue, consider the following options:

• Upgrade your solution to Sitecore XP 10.4.0 or later.
  
  
• For Sitecore XP 10.2:
  1. Download and install the corresponding cumulative hotfix available in KB1001439
  2. Back up the Reporting database.
  3. Delete all problematic records with the following script:

     DELETE
     FROM [xdb_processing_pools].[ContactProcessingPool]
     WHERE Attempts > 10

  4. Rebuild the Reporting database.
     
     
• For Sitecore XP 10.3:
  1. Download and install the corresponding cumulative hotfix available in KB1002844
  2. Back up the Reporting database.
  3. Delete all problematic records with the following script:

     DELETE
     FROM [xdb_processing_pools].[ContactProcessingPool]
     WHERE Attempts > 10

  4. Rebuild the Reporting database.
     
     
• For other versions:
  1. Back up the Reporting database.
  2. Delete all problematic records with the following script:

     DELETE
     FROM [xdb_processing_pools].[ContactProcessingPool]
     WHERE Attempts > 10

  3. Rebuild the Reporting database.