Video Screencast Help
Symantec to Separate Into Two Focused, Industry-Leading Technology Companies. Learn more.

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

Created: 05 Aug 2012 • Updated: 06 Aug 2012 | 7 comments
This issue has been solved. See solution.

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,

Comments 7 CommentsJump to latest comment

RahulG's picture

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's picture

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.

Dushan Gomez
IT Manager
VCP 4 and 5 | MCITP Exchange Server | MCTS SharePoint Server | MCP Windows XP

RahulG's picture
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's picture

Yes, it works man,

Thanks for the SQL query :-)

Dushan Gomez
IT Manager
VCP 4 and 5 | MCITP Exchange Server | MCTS SharePoint Server | MCP Windows XP

JesusWept3's picture

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
JesusWept3's picture

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
SOLUTION
Dushan Gomez's picture

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

Dushan Gomez
IT Manager
VCP 4 and 5 | MCITP Exchange Server | MCTS SharePoint Server | MCP Windows XP