cancel
Showing results for 
Search instead for 
Did you mean: 

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

Dushan_Gomez
Level 6

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,

1 ACCEPTED SOLUTION

Accepted Solutions

JesusWept3
Level 6
Partner Accredited Certified

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
https://www.linkedin.com/in/alex-allen-turl-07370146

View solution in original post

7 REPLIES 7

RahulG
Level 6
Employee

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.

Dushan_Gomez
Level 6

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.

RahulG
Level 6
Employee

 

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

 

Dushan_Gomez
Level 6

Yes, it works man,

Thanks for the SQL query :)

JesusWept3
Level 6
Partner Accredited Certified

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
https://www.linkedin.com/in/alex-allen-turl-07370146

JesusWept3
Level 6
Partner Accredited Certified

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
https://www.linkedin.com/in/alex-allen-turl-07370146

Dushan_Gomez
Level 6
Many thanks for the SQL script it works really great :)