Highlighted

Measuring throughput of Storage Expiry

Hi, reading through https://www-secure.symantec.com/connect/articles/gentle-approach-storage-expiry before we embark on a reduction on our site policy for expiry of items, and i wondered if there was a way to measure the number of items processed/removed, and load on servers etc whilst running this as a test ?

We wish to change storage expiry from 7 years to 2 years across 40000 archives, but initially will only target 1000 users and reduce from 7 to 6 years, to ensure we don't kill our infrastructure.

Is there a way to monitor the impact of this during the initial 3 hour run, as it will run in production hours (as we don't want it to run at the same time as the overnight archiving tasks).  so that we can then consider adding more archives to the new policy and trimming from 6 to 5,4,3, and eventually 2 years.

Thanks
Mark

3 Replies

yes, and it depends how

yes, and it depends how involved you want to get. EV has a few different moving parts such as the servers (cpu, memory), storage, sql, and indexes (which are also servers/storage/sql related.) monitoring for those aspects would primarily be done from outside of EV itself if you know what i mean.

you said you're also interested in measuring the number of items removed. a basic approach would be to run a vault store usage report (http://evserver/EnterpriseVault/usage.asp) before, during, and after the expiry window and compare as you go along.

lastly, the ev event log will have an event which reports how many items were expired during the allotted window each time the expiry schedule runs

You can query SQL for how

You can query SQL for how many items were expired, with a few caveats:

*Remove the -- before the AND JD.DeletionReason lines and change the dates accordingly if you wish to only see items expired during a specific date range

*This requires the Directory database to be on the same SQL server as the vault store DB; if not, you'll need to create a linked server pair relationship between the two SQL's and run from the SQL where the vault store DB is, passing in the SQL server name string in the query

*This will need a little tweak if you will be expiring savesets created with EV2007 or older, as the saveset naming convention changes a bit between v2007 and v8

*This includes a fair bit of extra information such as archive name;  you will get one row for each of the expired items which may be a rather big results set. You can change the query to drop the kinds of results you don't need, which may help give you fewer rows overall.

 

  USE <MyVaultStoreDB>
SELECT ArchiveName, (SUBSTRING(SavesetID, 17, 4) + '-' + SUBSTRING(SavesetID, 21, 2)) "Sent/Received Date",  
       COUNT(SavesetID) "Deleted Item Count"  
  FROM JournalDelete JD  Join EnterpriseVaultDirectory.dbo.ArchiveView AV
  ON AV.VaultEntryId = JD.ArchiveId
 WHERE SavesetID LIKE '%~Z~%'  
   AND JD.DeletionReason = 2   
   --AND JD.DeletionDate >= '2014-12-20 00:00:00.000'  
   --AND JD.DeletionDate <= '2014-12-25 23:59:59.000'  
GROUP BY ArchiveName, (SUBSTRING(SavesetID, 17, 4) + '-' + SUBSTRING(SavesetID, 21, 2))  
ORDER BY "Sent/Received Date" ASC

Here are some queries that

Here are some queries that may also help. They can show you what will be expired in the future. Set the NumDaysToCheck to whatever days of your retention category you want to forecast:

    DECLARE @NumDaysToCheck AS INT
    SET @NumDaysToCheck = -365
    
    --Items to Expire Per Retention Category:
    SELECT COUNT(S.IdTransaction) AS "Number of Savesets to Expire", RCE.RetentionCategoryName
    FROM Saveset S LEFT OUTER JOIN
    HoldSaveset HS ON HS.SavesetIdentity = S.SavesetIdentity INNER JOIN
    EnterpriseVaultDirectory.dbo.RetentionCategoryEntry RCE ON RCE.RetentionCategoryIdentity = S.RetentionCategoryIdentity
    WHERE HS.SavesetIdentity IS NULL AND S.ArchivedDate < DATEADD(Day, @NumDaysToCheck, GETUTCDATE()) AND RCE.OnHold = 0
    GROUP BY RCE.RetentionCategoryName
    
    
    
    --Items to Expire Per Archive Retention Category:
    SELECT COUNT(S.IdTransaction) AS "Number of Savesets to Expire", AV.ArchiveName, RCE.RetentionCategoryName
    FROM Saveset S LEFT OUTER JOIN
    HoldSaveset HS ON HS.SavesetIdentity = S.SavesetIdentity INNER JOIN
    EnterpriseVaultDirectory.dbo.RetentionCategoryEntry RCE ON RCE.RetentionCategoryIdentity = S.RetentionCategoryIdentity INNER JOIN
    ArchivePoint AP ON AP.ArchivePointIdentity = S.ArchivePointIdentity INNER JOIN
    EnterpriseVaultDirectory.dbo.ArchiveView AV ON AV.VaultEntryId = AP.ArchivePointId
    WHERE HS.SavesetIdentity IS NULL AND S.ArchivedDate < DATEADD(Day, @NumDaysToCheck, GETUTCDATE()) AND RCE.OnHold = 0
    GROUP BY RCE.RetentionCategoryName, AV.ArchiveName

 


Simply see what has expired:
SELECT COUNT(SavesetID) AS "Number of Deleted Items", MIN(DeletionDate) AS "First Deleteion", MAX(DeletionDate) AS "Last Deletion", ArchivePointIdentity, IndexCommitted, DeletionReason, DeletionStatus
FROM JournalDelete
GROUP BY ArchivePointIdentity, IndexCommitted, DeletionReason, DeletionStatus