Below is a sample SQL query that will list the archive name, the Archive ID, the total size in bytes to be removed, and the total number of items to be removed from the Vault Store specified.

 

USE 
SELECT ArchiveName, ArchivePointID, sum(ItemSize) as 'Total KB', count (*) as 'Num Items Eligible' FROM Saveset
INNER JOIN ArchivePoint
On saveset.ArchivePointIdentity = ArchivePoint.ArchivePointIdentity
INNER JOIN EnterpriseVaultDirectory.dbo.Root r on r.VaultEntryID = ArchivePointID
INNER JOIN EnterpriseVaultDirectory.dbo.Archive a on a.RootIdentity = r.RootIdentity
WHERE ArchivedDate <= (getdate() - 3)
GROUP BY ArchiveName, ArchivePointID

 

Change the name to the name of the Vault Store database to be queried. If there are multiple Vault Stores, then the query would have to be modified and run against each Vault Store database.

Note: This query is totaling the number of archived items per archive with an archived date older than 3 days ago. Change the number 3 to whatever number of days in the Retention Category used as the default category for the archive.

Using 3 days as an example, if the retention category is set to a period of 3 days, then after 3 days items could be expired from that category. With the query for 3 days, any items returned by the query would be eligible for expiry on the next run.