cancel
Showing results for 
Search instead for 
Did you mean: 

EV11 SQL query - retention and archive size

GertjanA
Moderator
Moderator
Partner    VIP    Accredited Certified

Hello all,

I have SQL queries that show the amount of items in an archive, the compressed, and the original size.

Would it be possible to use an SQL query to show the same per archive, but then with only the items and sizes which have been archived less than (example) 3 years ago?

In other words, what is the number of items, and compressed/uncompressed size of an archive for the items that are younger than 3 years (in that archive)

Thanks.

Gertjan.

Regards. Gertjan
1 ACCEPTED SOLUTION

Accepted Solutions

JesusWept3
Level 6
Partner Accredited Certified

This should work for you

SELECT A.ArchiveName "Archive Name",
       COUNT(S.itemSize) "Archived Items",
       SUM(S.ItemSize)/1024 "Compressed Size (MB)",
       SUM(CAST(SP.OriginalSize AS bigint))/1024/1024 "Uncompressed Size (MB)"
 FROM  EnterpriseVaultDirectory.dbo.Archive A WITH (NOLOCK),
       EnterpriseVaultDirectory.dbo.Root R WITH (NOLOCK),
       EVVSMyVaultStore_1.dbo.ArchivePoint AP WITH (NOLOCK),
       EVVSMyVaultStore_1.dbo.Saveset S WITH (NOLOCK),
       EVVSMyVaultStore_1.dbo.SavesetProperty SP WITH (NOLOCK)
 WHERE SP.SavesetIdentity = S.SavesetIdentity
   AND S.ArchivePointIdentity = AP.ArchivePointIdentity
   AND AP.ArchivePointId = R.VaultEntryId
   AND R.RootIdentity = A.RootIdentity
   AND S.ArchivedDate > DATEADD(YEAR, -3, GETDATE())
GROUP BY A.ArchiveName
ORDER BY A.ArchiveName ASC
https://www.linkedin.com/in/alex-allen-turl-07370146

View solution in original post

2 REPLIES 2

JesusWept3
Level 6
Partner Accredited Certified

This should work for you

SELECT A.ArchiveName "Archive Name",
       COUNT(S.itemSize) "Archived Items",
       SUM(S.ItemSize)/1024 "Compressed Size (MB)",
       SUM(CAST(SP.OriginalSize AS bigint))/1024/1024 "Uncompressed Size (MB)"
 FROM  EnterpriseVaultDirectory.dbo.Archive A WITH (NOLOCK),
       EnterpriseVaultDirectory.dbo.Root R WITH (NOLOCK),
       EVVSMyVaultStore_1.dbo.ArchivePoint AP WITH (NOLOCK),
       EVVSMyVaultStore_1.dbo.Saveset S WITH (NOLOCK),
       EVVSMyVaultStore_1.dbo.SavesetProperty SP WITH (NOLOCK)
 WHERE SP.SavesetIdentity = S.SavesetIdentity
   AND S.ArchivePointIdentity = AP.ArchivePointIdentity
   AND AP.ArchivePointId = R.VaultEntryId
   AND R.RootIdentity = A.RootIdentity
   AND S.ArchivedDate > DATEADD(YEAR, -3, GETDATE())
GROUP BY A.ArchiveName
ORDER BY A.ArchiveName ASC
https://www.linkedin.com/in/alex-allen-turl-07370146

GertjanA
Moderator
Moderator
Partner    VIP    Accredited Certified

Thanks JW.

Works like a charm.

Regards. Gertjan