SQL Query for Journal Archive Data Storage

[EVBrad]

I need to find out how much data I have in my Journal Archives in the last 5 years.  Is there a SQL Query I can run against each Journal Database to find out this info?  I found this script below which tells me the same for anything older than 3 years.  But, I need the last 5 years.  Thanks very much for the assistance.

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

[Ben_Watts]

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