Forum Discussion

Veritas_Resolve's avatar
Veritas_Resolve
Level 4
7 years ago

Deleting mails older than 7 years

Hi,

Guys,

My company is planning to implement a 7 yrs. retention policy for all the users.

Can someone please help me with a script that will show how much amount of data will be deleted after the policy is applied?

Thank you in advance.

  • GertjanA's avatar
    GertjanA
    7 years ago

    Hello,

    I assume you are going to expire based on the items date, not on the archived date (i.e. date of the mail, instead of date when mail was archived).

    I have below query, which I used in the past. I did not write this query, so if it fails, I won't be able to help.

    --
    --
    -- change VSDB to your VaultStoreDatabaseName
    --
    --
    SELECT A.ArchiveName "Archive Name",
           COUNT(S.IdTransaction) AS ArchivedItemCount,
           SUM(S.ItemSize)/1024 "Compressed Size (MB)",
           SUM(SP.OriginalSize)/1024/1024 "Uncompressed Size (MB)"
    FROM   EnterpriseVaultDirectory.dbo.Archive A,
           EnterpriseVaultDirectory.dbo.Root R,
           VSDB.dbo.ArchivePoint AP,
           VSDB.dbo.Saveset S,
           VSDB.dbo.SavesetProperty SP
    WHERE  A.RootIdentity = R.RootIdentity
      AND  R.VaultEntryId = AP.ArchivePointId
      AND  AP.ArchivePointIdentity = S.ArchivePointIdentity
      AND  S.SavesetIdentity = SP.SavesetIdentity
      AND  S.IdDateTime > DATEADD(year, -7, getDate())
    GROUP BY A.ArchiveName
    ORDER BY A.ArchiveName

  • GertjanA's avatar
    GertjanA
    Moderator
    7 years ago

    Hello,

    I assume you are going to expire based on the items date, not on the archived date (i.e. date of the mail, instead of date when mail was archived).

    I have below query, which I used in the past. I did not write this query, so if it fails, I won't be able to help.

    --
    --
    -- change VSDB to your VaultStoreDatabaseName
    --
    --
    SELECT A.ArchiveName "Archive Name",
           COUNT(S.IdTransaction) AS ArchivedItemCount,
           SUM(S.ItemSize)/1024 "Compressed Size (MB)",
           SUM(SP.OriginalSize)/1024/1024 "Uncompressed Size (MB)"
    FROM   EnterpriseVaultDirectory.dbo.Archive A,
           EnterpriseVaultDirectory.dbo.Root R,
           VSDB.dbo.ArchivePoint AP,
           VSDB.dbo.Saveset S,
           VSDB.dbo.SavesetProperty SP
    WHERE  A.RootIdentity = R.RootIdentity
      AND  R.VaultEntryId = AP.ArchivePointId
      AND  AP.ArchivePointIdentity = S.ArchivePointIdentity
      AND  S.SavesetIdentity = SP.SavesetIdentity
      AND  S.IdDateTime > DATEADD(year, -7, getDate())
    GROUP BY A.ArchiveName
    ORDER BY A.ArchiveName

    • CConsult's avatar
      CConsult
      Moderator
      7 years ago

      Hi,

      if you will need help or anthing does not work leave me a notice.

      the sql query will show you all data (for each archive in VaultstoreDatabase) which are older then 7 years and sums them.

      You can also easily sum them up in excel if you do not know how to do it in sql queries.

      the compressed size is what you will have less. 

      The query could take some time depending on how many archives you have.