cancel
Showing results for 
Search instead for 
Did you mean: 

Delete Archives with zero items

ZS1
Level 3

I have EV 11.0.1 running on SQL Server 2014 and am looking for a sql query to delete archives with zero items. We wont be upgrading to EV 12.

I can list those with the following query:

SELECT A.ArchiveName "Archive Name",
R.VaultEntryID "Archive ID",
AP.ModifiedDate "Last Modified",
AP.HighestIndexSeqNo "Highest Index Num",
AP.ArchivedItems "Archived Items"
FROM EnterpriseVaultDirectory.dbo.Archive A,
EnterpriseVaultDirectory.dbo.Root R,
EVVSEVMBAVS01_1.dbo.ArchivePoint AP
WHERE AP.ArchivePointId = R.VaultEntryID
AND R.RootIdentity = A.RootIdentity
AND AP.ArchivedItems = 0

I need to add the part where the ArchiveStatus will get changed from 1 to 4 (mark for deletion).

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

This worked.

 

UPDATE EnterpriseVaultDirectory.dbo.ArchiveView
SET ArchiveStatus = 4
FROM XXXXXXXX.dbo.ArchivePoint AS ap
JOIN EnterpriseVaultDirectory.dbo.ArchiveView AS av
ON av.VaultEntryId = ap.ArchivePointId
WHERE ap.ArchivedItems = 0

View solution in original post

5 REPLIES 5

TonySterling
Moderator
Moderator
Partner    VIP    Accredited Certified

Hey ya,

How many archives are you looking to delete?

This post might help you along: https://vox.veritas.com/t5/Enterprise-Vault/EV-SQL-script-to-change-archive-status-for-all-archives-...

TS

6000 plus

I think I have it

USE EnterpriseVaultDirectory
UPDATE Archive
SET ArchiveStatus = 4
WHERE RootIdentity = (
SELECT AP.ArchivedItems "Archived Items"
FROM EnterpriseVaultDirectory.dbo.Archive A,
EnterpriseVaultDirectory.dbo.Root R,
XXXXXXXX.dbo.ArchivePoint AP
WHERE AP.ArchivedItems = 0)

I will have a sql dba confirm it before running it.

 

That did not work either.

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

 

GertjanA
Moderator
Moderator
Partner    VIP    Accredited Certified

Hi,

I don't think SQL allows using UPDATE with a query after the WHERE.

You need to first find all archives that require deleting, then for each archive create the SQL statement.

As 'example', you find 10 archives. paste the rootidentity in an excel sheet in column 2. In column 1 place xxx, in column 3 place yyy. copy past the 3 columns into a notepad file. use find replace to replace xxx with the complete update statement (use A, update Z = '4' where V = ') and then find/replace yyy to set final closing '.

Then paste the full notepad content in a query window, select all lines, execute.

Regards. Gertjan

This worked.

 

UPDATE EnterpriseVaultDirectory.dbo.ArchiveView
SET ArchiveStatus = 4
FROM XXXXXXXX.dbo.ArchivePoint AS ap
JOIN EnterpriseVaultDirectory.dbo.ArchiveView AS av
ON av.VaultEntryId = ap.ArchivePointId
WHERE ap.ArchivedItems = 0