08-05-2012 09:30 PM
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,
Solved! Go to Solution.
08-06-2012 07:09 AM
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
08-06-2012 01:41 AM
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.
08-06-2012 01:44 AM
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.
08-06-2012 02:01 AM
08-06-2012 02:21 AM
Yes, it works man,
Thanks for the SQL query :)
08-06-2012 06:34 AM
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
08-06-2012 07:09 AM
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
08-06-2012 04:36 PM