EV11 - SQL uery for expiry

[GertjanA]

Hello all,

I am asked to figure out what would be expired from Journal Archives on 31-05-2017, based on the retention for the archive. I know the retention obviously. There is currently no expiry running.

I'm looking for a query that returns:

ArchiveName, Number of items, Compressed size, Uncompressed size.

The where should be based on the modified date of items, and then 'items which are archived more than 7 years ago from 31-05-2017'.

Does anyone have such a query at hand, or can provide one?

Thanks a mill.

[Pradeep-Papnai]

Hi Gertjan,

I have tested following query long back but it does not include compressed & uncompressed size of items, but it will give you total of item size per archive.

USE VSDB
Declare @YearToChange SmallInt
SET @YearToChange = -1
SELECT
AV.ArchiveName,
SUM (S.Itemsize) 'ItemSize(KB)',
COUNT(*) 'TotalItems'
FROM Saveset S
INNER JOIN ArchivePoint AP ON AP.ArchivePointIdentity=S.ArchivePointIdentity
INNER JOIN EnterpriseVaultDirectory.DBO.ArchiveView AV ON AV.VaultEntryId = AP.ArchivePointId
WHERE s.ArchivedDate<= DATEADD (YEAR, @YearToChange, getdate())
GROUP BY AV.ArchiveName

Regards
Pradeep Papnai