Video Screencast Help

Suggestion to optimized stored procedure - EVDeleteArchiveFolders

Created: 23 Mar 2012 • Updated: 28 Mar 2012 | 1 comment
Rune Helsinghof's picture
0 Agree
0 Disagree
0 0 Votes
Login to vote
Status: Implemented

Look in the zip file attached:
“Suggestion to optimized stored procedure - EVDeleteArchiveFolders.sql”
“Suggestion to optimized stored procedure - EVDeleteArchiveFolders post Etrack 1995642 hotfix.sql”
"Update @DeletedFolders_1.JPG"

Symantec Support requested me to upload this request here...

Error information:
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:

----------
UPDATE @DeletedFolders
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
END
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

Comments 1 CommentJump to latest comment

Andy Nash's picture

Thanks for the suggestion Rune,

I've investigated and we believe the issue your experiencing is fixed in 8.0.5, 9.0.3 and 10.0.1. So we'd recommend upgrading to one of these releases. If you continue to experience the same issue, please log another support case.

Note to all customers - Changing Enterprise Vault's stored procedures would invalidate your support contract and you may cause permentent damage to data you've archive. Thus, please do not modify any EV code without Symantec Support or Engineering approval.

If you do happen to spot an enhancement, please do ask support to log an enhancement request or add a new idea to the fourm.

Regards,
--Andy

+1
Login to vote