SQL Query to find archived messages & archive item size per mailbox user

You can use this query, it will tell you the following

1. Archive Name
2. Folder Path the item was archived under (i.e. \Sent Items)
3. Transaction ID (a unique identifier for the item)
4. IndexSeqNo (an identifier you can use to find the item easily via Search.asp)
5. ArchivedDate (The date the item was archived, in UTC, so 01/01/14 17:00 UTC would be 12PM EST)
6. IdDateTime (The Sent/Received Date of the item, again in UTC)
7. Item size, in KB

What it won't tell you is things like, who sent the item, who the item was sent to, subjects, names attachments, etc. Only the actual item and the index include those details.

So what you could do is if there are items you want to see, give yourself permission to the users archive, then go to http://yourEVServer/EnterpriseVault/Search.asp?advanced

Then take the "Item Sequence Number" thats posted here
then where it says "Sequence Number" From - To, just put in the number returned from the query and you will get the exact item

Also change EVVSYourVaultStore_1 to the Database name of your Vault Store, you should be able to see that pretty easily from your SQL Browser, but don't user EVVSGYourVaultStoreGroup_1_1

 

DECLARE	@sizeInMB int
DECLARE @sizeInB int

SET @sizeInMB = 25
SET @sizeInB = @sizeInMB*1024*1024

SELECT A.ArchiveName "Archive Name",
       REPLACE(CAST(AF.FolderPath AS Varchar(max)), '?', '\') "Folder Path",
       S.IdTransaction "Transaction ID",
       S.IndexSeqNo "Index Sequence Number",
       S.ArchivedDate "Archived Date",
       S.IdDateTime "Sent/Received Date",
       SP.OriginalSize/1024 "Size (KB)"
  FROM EnterpriseVaultDirectory.dbo.Archive A,
       EnterpriseVaultDirectory.dbo.ArchiveFolder AF,
       EnterpriseVaultDirectory.dbo.Root R1,
       EnterpriseVaultDirectory.dbo.Root R2,
       EVVSYourVaultStore_!.dbo.ArchivePoint AP,
       EVVSYourVaultStore_1.dbo.Vault V,
       EVVSYourVaultStore_1.dbo.Saveset S,
       EVVSYourVaultStore_1.dbo.SavesetProperty SP
 WHERE S.SavesetIdentity = SP.SavesetIdentity
   AND S.ArchivePointIdentity = AP.ArchivePointIdentity
   AND S.VaultIdentity = V.VaultIdentity
   AND AP.ArchivePointId = R1.VaultEntryId
   AND V.VaultID = R2.VaultEntryId
   AND R1.RootIdentity = A.RootIdentity
   AND R2.RootIdentity = AF.RootIdentity
   AND SP.OriginalSize >= @sizeInB