Forum Discussion

Dushan_Gomez's avatar
13 years ago
Solved

How to know how much data that is older than 3 years archived by Enterprise Vault ?

Hi Everyone,

I need the SQL script to query my Enterprise Vault v8.0 SP4 that is older than 3 years. Can anyone share your SQL script to know how much disk in the vault it consumes that'd be great.

Thanks,

  • Actually nevermind, I didn't read the "older than 3 years" requirement
    In which case it would be

    SELECT A.ArchiveName, 
           COUNT(S.ItemSize) "No. Archived Items",
           SUM(S.ItemSize)/1024 "Total Size (MB)
    FROM   yourVaultStore.dbo.Saveset S,
           yourVaultStore.dbo.ArchivePoint AP,
           EnterpriseVaultDirectory.dbo.Root R,
           EnterpriseVaultDirectory.dbo.Archive A
    WHERE  S.ArchivePointIdentity = AP.ArchivePointIdentity
      AND  AP.ArchivePointId = R.VaultEntryId
      AND  R.RootIdentity = A.RootIdentity
      AND  S.IdDateTime < DATEADD(year, -3, getDate())
    GROUP BY A.ArchiveName
    ORDER BY A.ArchiveName
  • Well when you browse the path for the vault store partition , you will see folder created with Year, month and date,

    You can simply go to the property of the flder and see the size.

  • Yes, I can see it from there, but I wonder how much in total that is why I need the SQL script so that th result can be calculated or SUM in Excel.

  •  

    YOu can use the following query to find the size and number of archvied item per archive.
     
    Use VaultStoreName
    select archivename
    ,count(*) as ArchivedItems
    ,sum(itemsize) as ArchivedItemsSize
    ,max(indexseqno) as maxindexseqno
    from enterprisevaultdirectory.dbo.root r1
    inner join enterprisevaultdirectory..root r2 on r1.rootidentity = r2.containerrootidentity
    inner join enterprisevaultdirectory.dbo.archive a1 on r1.rootidentity = a1.rootidentity
    inner join vault on r2.vaultentryid = vault.Vaultid
    inner join saveset on vault.vaultidentity = saveset.vaultidentity
    group by archivename

     

  • Honestly that script is a little overkill....
    your best bet is http://yourEVServer/EnterpriseVault/Usage.asp and that will give you things like avg item size etc as well as the number of items and size of archive etc.

    But really a much easier and more efficient query would be

    SELECT A.ArchiveName, AP.ArchivedItemsSize, AP.ArchivedItems
    FROM   yourVaultStore.dbo.ArchivePoint AP,
           EnterpriseVaultDirectory.dbo.Root R,
           EnterpriseVaultDirectory.dbo.Archive A
    WHERE  AP.ArchivePointId = R.VaultEntryId
      AND  R.RootIdentity = A.RootIdentity
    
  • Actually nevermind, I didn't read the "older than 3 years" requirement
    In which case it would be

    SELECT A.ArchiveName, 
           COUNT(S.ItemSize) "No. Archived Items",
           SUM(S.ItemSize)/1024 "Total Size (MB)
    FROM   yourVaultStore.dbo.Saveset S,
           yourVaultStore.dbo.ArchivePoint AP,
           EnterpriseVaultDirectory.dbo.Root R,
           EnterpriseVaultDirectory.dbo.Archive A
    WHERE  S.ArchivePointIdentity = AP.ArchivePointIdentity
      AND  AP.ArchivePointId = R.VaultEntryId
      AND  R.RootIdentity = A.RootIdentity
      AND  S.IdDateTime < DATEADD(year, -3, getDate())
    GROUP BY A.ArchiveName
    ORDER BY A.ArchiveName