cancel
Showing results for 
Search instead for 
Did you mean: 

SQL Query Identify Orphan Archives

devilstonic
Level 2

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. 

 

1 ACCEPTED SOLUTION

Accepted Solutions

AndrewB
Moderator
Moderator
Partner    VIP    Accredited

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

 

View solution in original post

6 REPLIES 6

AndrewB
Moderator
Moderator
Partner    VIP    Accredited

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

 

devilstonic
Level 2

Fantastic! Thank you for the quick response. I will give it a try.

devilstonic
Level 2

One question is the itemsize as reported in the saveset table in bytes? At first glance this will do exactly what I need. 

AndrewB
Moderator
Moderator
Partner    VIP    Accredited

itemSize is the size in EV in KB so itemSize/1024 would be in MB

devilstonic
Level 2

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. :)

AndrewB
Moderator
Moderator
Partner    VIP    Accredited

Anytime ;)