Severe blockings can occur on the SQL Server during Enterprise Vault Indexing

Problem

In Enterprise Vault (EV) 12. x, the execution of certain Stored Procedures during the EV Indexing process can cause severe blocking on the SQL Server resulting in poor performance and increase in the backlog of items awaiting indexing.

The following Stored Procedures have been identified:

1. uspd_ClearActionsFromLog
2. uspu_RollbackCommittedWork
    

The executions of the Stored Procedures will timeout by default after 120 seconds and will be retried  40 times raising Information Event  41619

Error Message

The following error can be seen when the execution of a stored procedure timed out in the EV event log

Type :        Information
Event :        41619
Source :        Enterprise Vault
Category :    Index Volumes Processor
User :        N/A
Computer :    EVServer
Description:
A transient error is preventing the execution of a SQL command. Enterprise Vault will try to run the command again.
Command: 'EVSqlCommand: DB: Data Source=SQLServer;Database=EVVSAaultStore_1;Integrated Security=true;Connect Timeout=120 - Command: uspu_RollbackCommittedWork'
Attempt: 1 of 40.
Last Error: Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
V-437-41619   

Cause

Those stored procedures performed changes (updates and deletions)  to certain tables such as JournalDelete, JournalArchive and ItemAdditionStatusLog.

On certain occasions, the update and\or deletion criteria can result in a large number of rows being selected (from several hundreds thousands to several millions of records) and the changes are attempted in one transaction resulting in the long duration execution of the Stored Procedures and ensuing blocking.

When the execution times out after 120 secs, all transactions are rolled back, and at the next retry, the process is repeated again with no items actually ever being updated or deleted

Solution

This issue has been resolved in the following releases:

Enterprise Vault 12.5.3

Enterprise Vault 14.0.0

Downloadable here.