SQL query to search for expired savesets?

Well the Storage Expiry report on the server will use SQL queries and will break it down via the retention category

for instance:
lets say you expire items older than 90 days,
Modified Date/Sent Date query would be like

SELECT COUNT(*) FROM Saveset WHERE idDateTime < getDate(now()-90)

Based on Archived Date it would be

SELECT COUNT(*) FROM Saveset WHERE ArchivedDate < getDate(now()-90)


However the expiry report that is used through the buttons in outlook actually use the users index to determine what items are being expired, how many etc

So through SQL it's easy, on a per user basis its easy, but for everyone?
the best you can do to simulate that is to go to http://yourEVServer/EnterpriseVault/search.asp?advanced

then you can do a search based using dates against everyones archive, that should give you a list
just as long as the items are not on hold and the user is enabled for expiry

Hi JW, thanks for the detailed response. :) So getting an expiry report isn't so simple.. About the savesets by modified date is it easy to include which vaulid/user is the saveset from? Let me try this out and check the saveset table for what columns it has. Thanks again. Merve

yeah thats easy enough, you could do the following


SELECT EME.mbxDisplayName, COUNT(S.IdTransaction) AS ItemsToExpire

FROM EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry EME,
             EVVaultStoreDB.dbo.Saveset S,
             EVVaultStoreDB.dbo.ArchivePoint AP

WHERE S.ArchivePointIdentity = AP.ArchivePointIdentity
       AND AP.ArchivePointId = EME.DefaultVaultId
       AND S.IdDateTime < getDate() - 90

GROUP BY EME.MbxDisplayName


getDate()-90 should be however many days your expiry is set to
if you wanted to change this to use Archive Date and not modified Date, you'd use "AND S.ArchivedDate < getDate()"

Another caveat to the script above is that its only for Enabled users in the ExchangeMailboxEntry table, because its simpler to write that pushing across to the Root/Vault/Archive tables.

If you want to exclude items that aren't on hold (if using Discovery Accelerator) you can link to the SavesetHold table and use a NOT query using SavesetIdentity and ArchivePointIdentity

Lastly!
When enterprise vault runs storage expiry, it calculates it to the very second

So lets say you are targeting 90 days ago
if you run expiry in the morning it may say it has 100 items to expire, but lets say 90 days ago in the afternoon you imported a PST file , you might find if you run the expiry a few hours later, it comes back with 10000 items to expire