There are two approaches to determining whether an archive has zero items.
1) Count up all the items in the Saveset table that belong to each archive. This is what Gertjan's query is doing. This is always going to be accurate, but may be time consuming to run.
2) Use the statistics in the ArchivePoint table. This table tracks the number and cumulative size of items in each archive. It is much faster to query this data than to count and sum from the Saveset table; however, while uncommon, it is possible for these statistics to be incorrect. (We have EVSVR operations to repair these statistics, which are referred to as "Reference Counts" or "RefCounts" in the EVSVR program text and documentation.)
Here is a statement to select all Archives with zero items, using the first method:
SELECT ap.ArchivePointId, av.ArchiveName, COUNT(s.SavesetIdentity) AS ItemCountFromSaveset
FROM ArchivePoint AS ap
JOIN EnterpriseVaultDirectory.dbo.ArchiveView AS av
ON av.VaultEntryId = ap.ArchivePointId
LEFT OUTER JOIN Saveset AS s
ON s.ArchivePointIdentity = ap.ArchivePointIdentity
GROUP BY ap.ArchivePointId, av.ArchiveName
HAVING COUNT(s.SavesetIdentity) = 0
Here is a statement to select all Archives with zero items, using the second method:
SELECT ap.ArchivePointId, av.ArchiveName, ap.ArchivedItems
FROM ArchivePoint AS ap
JOIN EnterpriseVaultDirectory.dbo.ArchiveView AS av
ON av.VaultEntryId = ap.ArchivePointId
WHERE ap.ArchivedItems = 0
Here is a statement to mark for deletion all Archives with zero items, using the first method:
UPDATE EnterpriseVaultDirectory.dbo.ArchiveView
SET ArchiveStatus = 4
WHERE VaultEntryId IN (
SELECT ap.ArchivePointId
FROM ArchivePoint AS ap
JOIN EnterpriseVaultDirectory.dbo.ArchiveView AS av
ON av.VaultEntryId = ap.ArchivePointId
LEFT OUTER JOIN Saveset AS s
ON s.ArchivePointIdentity = ap.ArchivePointIdentity
GROUP BY ap.ArchivePointId
HAVING COUNT(s.SavesetIdentity) = 0
)
Here is a statement to mark for deletion all Archives with zero items, using the second method:
UPDATE EnterpriseVaultDirectory.dbo.ArchiveView
SET ArchiveStatus = 1
FROM ArchivePoint AS ap
JOIN EnterpriseVaultDirectory.dbo.ArchiveView AS av
ON av.VaultEntryId = ap.ArchivePointId
WHERE ap.ArchivedItems = 0
Hope this helps you out.
--Chris