The other day I had a need to find out how many archived items were in a particular archive. Normally I'd leap straight over to the Vault Store Usage Report, but for some reason (maybe the number of archives) that just seemed to take 5+ minutes to display. Needless to say, I gave up on the report, for now.
But I still needed to know how many items were in the archive I was interested in.
I used this query in the end:
SELECT A.ArchiveName, AP.ArchivedItems, AP.ArchivedItemsSize
FROM EnterpriseVaultDirectory.dbo.Archive A, EnterpriseVaultDirectory.dbo.Root R, VSDB.dbo.ArchivePoint AP
WHERE AP.ArchivePointId = R.VaultEntryId AND R.RootIdentity = A.RootIdentity
AND A.ArchiveName = 'User'
All you have to do is change the VSDB to the name of the Vault Store database you're interested in (or, remove it and the .dbo. and launch the query from the Vault Store database), and on the last line put in the User you're interested in.
Problem solved, and...