SQL Query for Journal Archive Data Storage

Hi Brad,

If that does pull back the last 3 years of data simply change it to the below so that it pulls back 5 years of data:-

SELECT A.ArchiveName, 
       COUNT(S.ItemSize) "No. Archived Items",
       SUM(S.ItemSize)/1024 "Total Size (MB)"
FROM   Saveset S,
       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, -5, getDate())
GROUP BY A.ArchiveName
ORDER BY A.ArchiveName

I have also removed the reference to the Vault Store DB, simply run the query against your Vault Store DB.

If you want to limit the results you can also add to the WHERE clause at the bottom to target just the Journal Archive, obviously change the name of the Archive to match the name for your Journal Archive, for example:-

SELECT A.ArchiveName,
   COUNT(S.ItemSize) "No. Archived Items",
   SUM(S.ItemSize)/1024 "Total Size (MB)"
FROM  Saveset S,
   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, -5, getDate())
AND ArchiveName = 'Journal'
GROUP BY A.ArchiveName
ORDER BY A.ArchiveName

Regards

Ben