Input around EV Storage Expiry impact

[Bruce_Crankshaw]

Afternoon All
I have a customer who when they enabled Storage Expiry realized that some Journal data had been incorrectly stamped with the wrong Retention Category .The result is that 2.3 Million items have been deleted off Centera and the Journal Archives .There Compliance division has asked for report around this and I would like some input around the questions and how I can best get the required information.The questions I'm unsure of  are
 

1.How can I see how much data is actually stamped with the incorrect Retention Category

I used Browser search and  specified the incorrect Retention Category to see how much was marked  incorrectly.It did show me results but is there a better way to query Vault Store or Journal Archives ?I'm thinking a SQL query against the relevant Vault Store  ?

2.  Is the Storage Expiry sequential ?
Does EV delete from Oldest to Newest is respect to data that has exceeded the the Retention Period ?

3.Is there a way to see when this Retention Category was applied and for how long
Once again I am using Browser Search and from that I can assume I can see the date ranges ..but is there a more elegant way to confirm this ?

They don't have DA and are running EV 8.0 sp3

Thanks in advance
 

[JesusWept3 · Accepted solution]

1. .How can I see how much data is actually stamped with the incorrect Retention Category

For the retention category you can do a SQL Statement such as this

SELECT COUNT(S.IdTransaction) AS ItemCount, RCE.RetentionCategoryName, RCE.RetentionPeriod, RCE.RetentionPeriodUnits
FROM EnterpriseVaultDirectory.dbo.RetentionCategoryEntry RCE,
            YourVaultStoreDB.dbo.Saveset S
WHERE S.RetentionCategoryIdentity = RCE.RetentionCategoryIdentity
GROUP BY RCE.RetentionCategoryName, RCE.RetentionPeriod, RCE.RetentionPeriodUnits

You need to run this against each vault store, swapping out "yourVaultStoreDB.dbo.Saveset" to be the name of your Vault Store database

RetentionPeriodUnits will be
0 = days
1 = weeks
2 = months
3 = years

If you just want to see the wrong retention, add the following under the WHERE clause and above the group by

AND RCE.RetentionCategoryName = 'wrong Retention'

2.  Is the Storage Expiry sequential
Storage Expiry will run per retention category per vault store going from whats listed in the database first. so if you have
VaultStore1, VaultStore2 etc, it will go by what ever is listed in the db first (if vaultstore2 was added before vaultstore1 then it would go with vault store 2 first)

I don't believe that the expiry process does any kind of ordering, but i would expect either way it would in fact do the oldest first as that makes the most logical sense

3.Is there a way to see when this Retention Category was applied and for how long?
Not really unless you're willing to look through aimless transaction logs from SQL all day long.
I guess the thing is, the retention category thats being used, should it not be used or is it used for other people?
Assuming they're not doing an update on retention category for items, you could run the following query

SELECT TOP 10 S.ArchivedDate, RCE.RetentionCategoryName
FROM EnterpriseVaultDirectory.dbo.RetentionCategoryEntry RCE,
             YourVaultStoreDB.dbo.Saveset S
WHERE S.RetentionCategoryIdentity = RCE.RetentionCategoryIdentity
       AND RCE.RetentionCategoryName = 'wrong Retention'
ORDER BY S.ArchivedDate ASC

If you want to see the newest Archived Item, change the ORDER BY to DESC instead of ASC
But i'm not really sure it proves anything if people are meant to be using the retention category.
If you have a specific archive in mind, then you could go one step further (if this was a journal archive)

SELECT TOP 10 A.ArchiveName, S.ArchivedDate, RCE.RetentionCategoryName
FROM EnterpriseVaultDirectory.dbo.Archive A,
             EnterpriseVaultDirectory.dbo.Root R,
            EnterpriseVaultDirectory.dbo.RetentionCategoryEntry RCE,
            yourVaultStoreDB.dbo.Saveset S,
            yourVaultStoreDB.dbo.ArchivePoint AP
WHERE S.RetentionCategoryIdentity = RCE.RetentionCategoryIdentity
      AND S.ArchivePointIdentity = AP.ArchivePointIdentity
      AND AP.ArchivePointID = R.VaultEntryID
      AND R.RootIdentity = A.RootIdentity
      AND RCE.RetentionCategoryName = 'wrong Retention'
      AND A.ArchiveName = 'my Journal Archive'
ORDER BY S.ArchivedDate DESC

So the above would prove that in the archive 'my Journal Archive' how old the oldest item stamped with the wrong retention is, however due to the fact that you have already run storage expiry, its bound to be newer than those already deleted

[Bruce_Crankshaw]

@ JesusWept
You are a genius ...thx
I'm not sure if I understand all this but I'll go through it with the SQL DBA
Really appreciate the input :)