06-28-2016 07:09 AM
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
06-30-2016 01:03 AM
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