Duplicate Archive Details

In certain circumstances when an exchange mailboxes are re enabled for archiving it get 'DefaultVaultId' from ‘ExchangeMailboxEntry’ table of directory database. A duplicate archive may get create if 'DefaultVaultId' is blank at the time when mail-box is re-enabled. Below SQL queries are helpful for Administrator if they need duplicate archive details to know the scope of issue if this is happen in larger scale.

 

Get count of each duplicate archive have environment:-

USE EnterpriseVaultDirectory

 SELECT ArchiveName, ArchiveDescription, COUNT(*) AS CountOf

       FROM  ArchiveView

 WHERE Type <> 129   

 GROUP BY ArchiveName, ArchiveDescription

       HAVING COUNT(*) > 1

 

Get Archive Name, Archive description, ArchiveID with associated Vault Store & Storage server.

USE EnterpriseVaultDirectory

 SELECT AV.ArchiveName, AV.ArchiveDescription, AV.VaultEntryId ArchiveID, vse.VaultStoreName, ce1.ComputerName Storage_Server

       FROM EnterpriseVaultDirectory.dbo.ArchiveView AV

       JOIN

       (

              SELECT ArchiveName, ArchiveDescription, COUNT(*) AS CountOf

              FROM EnterpriseVaultDirectory.dbo.ArchiveView

              WHERE Type <> 129    GROUP BY ArchiveName, ArchiveDescription

              HAVING COUNT(*)>1

              ) dt ON av.ArchiveName=dt.archivename

       JOIN EnterpriseVaultDirectory.dbo.VaultStoreEntry vse on vse.VaultStoreEntryId = av.VaultStoreEntryId

       JOIN EnterpriseVaultDirectory.dbo.StorageServiceEntry se on se.ServiceEntryId = vse.StorageServiceEntryId

       JOIN EnterpriseVaultDirectory.dbo.ComputerEntry ce1 on ce1.ComputerEntryId = se.ComputerEntryId

 ORDER BY av.ArchiveName

 

Get details of old & new archives such as creation/modified date, Total items, Total Size (MB) and Archive type, Following query need to run against Vault Store Database.

USE VaultStore

 SELECT

 AV.Archivename, AV.ArchiveDescription, AV.VaultEntryId ArchiveID, AP.CreatedDate Archive_CreationDate,

 AP.ModifiedDate Archive_ModifiedDate, AP.ArchivedItems Total_Items, AP.ArchivedItemsSize/1024 'Size (MB)',

     CASE AV.Type

         WHEN 5 THEN 'Shared'

         WHEN 9 THEN 'Mailbox'

         WHEN 17 THEN 'Journal'

         WHEN 33 THEN 'Public Folder'

         WHEN 65 THEN 'SharePointServer'

         WHEN 129 THEN 'FSA'

         WHEN 257 THEN 'Sharepoint'

         WHEN 513 THEN 'LotusJournal'

         WHEN 1025 THEN 'LotusMbx' ELSE 'Undefined' END AS ArchiveType

 FROM EnterpriseVaultDirectory.dbo.ArchiveView av

 JOIN

    (

        SELECT ArchiveName, ArchiveDescription, COUNT(*) AS CountOf

        FROM EnterpriseVaultDirectory.dbo.ArchiveView

        WHERE Type <> 129 GROUP BY ArchiveName, ArchiveDescription

        HAVING COUNT(*)>1

    ) DT ON av.ArchiveName=dt.ArchiveName

 JOIN ArchivePoint AP on ap.ArchivePointId = av.VaultEntryId

 ORDER BY AV.ArchiveName