Forum Discussion

GertjanA's avatar
GertjanA
Moderator
6 years ago

Find - Which retention does an archive has?

Hello all,

I have an environment which is old (11.0.1CHF5) and static. Most configuration information is removed from EV. The databases are available. Services are running, sometime a DA search is done.

I need to find out which archive has which retention assigned. How can I derive this from SQL?

Does anyone know?

  • TonySterling's avatar
    TonySterling
    6 years ago

    This might help you:

    --This gives the archive name and count of items by retention cat.
    --Runs against the VaultStore Database
    SELECT a.ArchivePointID, ex.MbxDisplayName, s.ArchivePointIdentity, count(s.) as 'Number of Items', sum (s.itemsize/1024/1024) as 'Size'
    FROM Saveset s
    Join Archivepoint a on a.ArchivePointIdentity =s.ArchivePointIdentity
    Join EnterpriseVaultDirectory.dbo.RetentionCategoryEntry rc on rc.RetentionCategoryIdentity = s.RetentionCategoryIdentity
    Join EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry ex on ex.DefaultVaultID = a.ArchivePointID
    GROUP BY ArchivePointID,s.ArchivePointIdentity, rc.RetentionCategoryName, ex.MbxDisplayName

  • TonySterling's avatar
    TonySterling
    Moderator
    6 years ago

    This might help you:

    --This gives the archive name and count of items by retention cat.
    --Runs against the VaultStore Database
    SELECT a.ArchivePointID, ex.MbxDisplayName, s.ArchivePointIdentity, count(s.) as 'Number of Items', sum (s.itemsize/1024/1024) as 'Size'
    FROM Saveset s
    Join Archivepoint a on a.ArchivePointIdentity =s.ArchivePointIdentity
    Join EnterpriseVaultDirectory.dbo.RetentionCategoryEntry rc on rc.RetentionCategoryIdentity = s.RetentionCategoryIdentity
    Join EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry ex on ex.DefaultVaultID = a.ArchivePointID
    GROUP BY ArchivePointID,s.ArchivePointIdentity, rc.RetentionCategoryName, ex.MbxDisplayName

    • GertjanA's avatar
      GertjanA
      Moderator
      6 years ago

      Thanks Tony!

      What should be : SELECT a.ArchivePointID, ex.MbxDisplayName, s.ArchivePointIdentity, count(s.???) as 'Number of Items', sum (s.itemsize/1024/1024) as 'Size'

      I used SaveSetIdentity

      The query is close, but not completely. I want to know which retention category the archives have, not how much items I have. I added rc.retentioncategoryname to the select, now shows retention cat.

      SELECT a.ArchivePointID, ex.MbxDisplayName, s.ArchivePointIdentity, count(s.savesetidentity) as 'Number of Items', sum (s.itemsize/1024/1024) as 'Size', rc.retentioncategoryname

       

       

      • TonySterling's avatar
        TonySterling
        Moderator
        6 years ago

        Sorry about the incompleteness, looks like my saved query got updated with one I was customizing.