SQL Query Identify Orphan Archives
Good afternoon. I am new to Enterprise Vault and unfortunately the only access i have to the environment in question is read only access to the EV Database. I don't have access to the EV console.
I need to know if there is a way to query EV SQL to identify all of the orphaned archives (Archives that no longer have a mailbox associated) and the sizes of those archives. This environment is running EV 9
If someone could be so kind as to point me to the tables that might contain this data, I would really appreciate it. I tried to do a search in the existing posts for the information that i need, but i don't see what i'm looking for.
Thanks in advance.
Here's a script you can run. Replace <Vault Store Database> with the name of each EV Vault Store Database you want to query. I've also included a little legend for you at the bottom which explains the different codes you'll get back for MbxExchangeState and MbxArchivingState. You can then filter the results however you want depending on what you deem to be "orphaned" in your environment.
USE <Vault Store Database>
SELECT ex.MbxDisplayName, ex.MbxExchangeState, ex.MbxArchivingState,
a.archiveditems as Count, "Size" =sum(Convert(bigint,s.itemSize))/1024
FROM Saveset s
Join Archivepoint a on a.ArchivePointIdentity =s.ArchivePointIdentity
Join EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry ex on ex.DefaultVaultID = a.ArchivePointID
GROUP BY ex.MbxDisplayName, ex.MbxExchangeState, ex.MbxArchivingState, a.archiveditems, a.archiveditemssize-- MbxExchangeState
-- 0 = NORMAL, 1 = DELETED, 2 = HIDDEN-- MbxArchivingState
-- 0 = NONE, 1 = ENABLED, 2 = DISABLED