SQL Query to find archived messages & archive item size per mailbox user
- 11 years ago
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 itemAlso 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