cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted

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. 

 

1 Solution

Accepted Solutions
Highlighted
Accepted Solution!

Here's a script you can run.

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
Highlighted
Accepted Solution!

Here's a script you can run.

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

Highlighted

Fantastic! Thank you for the

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

Highlighted

One question is the itemsize

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

Highlighted

itemSize is the size in EV in

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

Highlighted

That's exactly what I needed.

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. Smiley Happy

Highlighted

Anytime ;)

Anytime Smiley Wink