10-19-2015 09:53 AM
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.
Solved! Go to Solution.
10-19-2015 10:44 AM
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
10-19-2015 10:44 AM
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
10-19-2015 10:58 AM
Fantastic! Thank you for the quick response. I will give it a try.
10-19-2015 11:11 AM
One question is the itemsize as reported in the saveset table in bytes? At first glance this will do exactly what I need.
10-19-2015 11:25 AM
itemSize is the size in EV in KB so itemSize/1024 would be in MB
10-19-2015 11:30 AM
That's exactly what I needed. Thank you very much for your help. I love it when I get faster answers from kind people like you than i do from the people in my own company who are supposed to know EV. :)
10-19-2015 11:43 AM
Anytime ;)