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,

1 Solution

Accepted Solutions
Highlighted
Accepted Solution!

Actually nevermind, I didn't

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
Highlighted

Well when you browse the path

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.

Highlighted

Yes, I can see it from there,

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.

Highlighted

  YOu can use the following

 

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

 

Highlighted

Yes, it works man, Thanks for

Yes, it works man,

Thanks for the SQL query :-)

Highlighted

Honestly that script is a

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
Highlighted
Accepted Solution!

Actually nevermind, I didn't

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

Highlighted

This is what I need.

Many thanks for the SQL script it works really great :-)