Report on how much EV will expire on a certain date?

You could use something like this SQL query
 

--This gives number of items and average size for items older than a certain year
--Runs against the VaultStore Database
select count(*) as 'Number of Items over 7 Years', "Av Size" = sum (itemsize)/count (*)
from saveset
where IdDateTime < dateadd("YY", -7, getdate ())

Hi Gogeta,

What version of Enterprise Vault are you using? In the Enterprise Vault Site Settings > Storage Expiry tab, take a look and run the Storage Expiry report in Report Mode. It should create an event ID in the event viewer with more details about how many items would be expired based on the Vault Store and Retention Category.

I hope this helps.

--set the number of days in @daystillexpiry parameter(like 1 month = 30 days) after expiry status, set to 0/1 (0=all archives, 1=only archives with “delete expired items” on) in @expiryarchivesonly parameter.  

And run below query against vault store database.

--------------------------

declare @daystillexpiry smallint
set @daystillexpiry = '600'
 
declare @expiryarchivesonly bit
set @expiryarchivesonly = '0'

SELECT a.ArchiveName, s.ArchivePointIdentity, RCE.RetentionCategoryName,sum(ItemSize)as TotalKB, count (*) as 'Num Items Eligible' FROM Saveset S
inner join ArchivePoint
On S.ArchivePointIdentity = ArchivePoint.ArchivePointIdentity
INNER JOIN EnterpriseVaultDirectory.dbo.Root r on r.VaultEntryID = ArchivePointID
INNER JOIN EnterpriseVaultDirectory.dbo.Archive a on a.RootIdentity = r.RootIdentity
join EnterpriseVaultDirectory.dbo.RetentionCategoryEntry RCE on S.RetentionCategoryIdentity = RCE.RetentionCategoryIdentity
where RCE.retentionperiod < '99999' and datediff(dd,getdate(),(dbo.fnExpirationDate(-(rce.retentionperiod),(rce.retentionperiodunits),(s.iddatetime))))< @daystillexpiry and a.deleteexpireditems >= @expiryarchivesonly
GROUP BY ArchiveName, ArchivePointID, RCE.RetentionCategoryName, s.ArchivePointIdentity
Order by archivename Asc

Thank you for the SQL query. 

If I understand the query correctly, the getdate() gives me the current date, so the query gives me how much will expire today?  I actually don't expect anything to expire until a few months from now.  However, the query returned a number when I ran it. 

So I am wondering what is the idDateTime?  Is it the date that the email was archived by Evault?  Or is it the original sent date of the email?  It would make sense if it is comparing the original sent date with the current date because some sent dates are much older than 7 years, otherwise I should get zero when I run it today because nothing has been in Evault for more than 7 years until a few months later.

Any advice will be appreciated.

Thank you.

Yes, our retention is based on the archived date.   It is reporting what we expect now. 

Thanks for your help.