Forum Discussion

devilstonic's avatar
devilstonic
Level 2
9 years ago

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.  ...
Enterprise Vault
Information Governance
Search & Analysis
Windows Server (2003-2008)
  • AndrewB's avatar
    AndrewB
    9 years ago

    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

     

AndrewB's avatar
AndrewB
Moderator
9 years ago

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