Suggestion to optimized stored procedure - EVDeleteArchiveFolders
Look in the zip file attached:
“Suggestion to optimized stored procedure - EVDeleteArchiveFolders.sql”
“Suggestion to optimized stored procedure - EVDeleteArchiveFolders post Etrack 1995642 hotfix.sql”
Symantec Support requested me to upload this request here...
3 weeks ago our EV system started to stop working in phases of ~30 min and comes back online again.
Vault Search and Archive Explorer is working but users cannot open items.
Vault Cache files also stops to be created on the EV server when the system is in the error state.
We have found a SQL call (“UPDATE @DeletedFolders”) that builds up and reach a point where it stalls the EV application and all new requests to the EV system (Item retrieval, vault cache sync file, etc…)
The “UPDATE @DeletedFolders” now holds several hundred waiting tasks on the SQL server.
If we kill the SQL call the EV system returns to normal operation until the call builds up again and stalls the system.
Inside the call it looks up against all items in the EV database several times (that is 77 mil. Items in our case) that consumes a lot of CPU on the SQL server and build up the waiting tasks on the SQL server to a deadlock.
Our system information:
EnterpriseVault 8 SP4 for Exchange
OS: Windows Server 2003 Standard edition (32 bit) SP2
Here is the SQL stored procedure call that stalls:
SET Result = CASE WHEN Vault.VaultId IS NOT NULL THEN 0x80041BF3 -- STORAGE_W_JOURNAL_ITEMS_IN_FOLDER
WHEN Vault.VaultId IS NULL THEN 0 -- The vault has already been deleted successfully
FROM @DeletedFolders df
LEFT JOIN Vault
ON Vault.VaultId = df.VaultEntryId
LEFT JOIN view_Vault_Unreferenced VU
ON Vault.VaultIdentity = VU.VaultIdentity
WHERE Vault.ArchivedItems = 0
AND VU.VaultId IS NULL -- the items NOT in the unreferenced list...
Stored procedure suggestions written by my colleague Carsten