cancel
Showing results for 
Search instead for 
Did you mean: 

SQL Query to get Archive size

GertjanA
Level 6
Partner    VIP    Accredited Certified

Hello all,

I am sure I have seen these kind of queries, but cannot locate them.I am (un)fortunately not an SQL-person, so I am unable to compose such a query myself...

I need to get a list that shows the number of items and the size of certain archives.

We currently do not have SQL-reporting available. The usage-report is to slow to be used due to the amount of archives.

Does anyone have a query at hand that will allow me to show this info quickly?

Thanks!

Gertjan

Regards. Gertjan
1 ACCEPTED SOLUTION

Accepted Solutions

JesusWept3
Level 6
Partner Accredited Certified

A slower, slightly more silly query would be the following (but might be more accurate and what you're looking for

 

SELECT A.ArchiveName, 
       COUNT(S.IdTransaction) "Num. Items Archived",
       SUM(S.ItemSize)/1024 "Archived Items Size (MB)",
       SUM(SP.OriginalSize)/1024/1024 "Original Email Size (MB)"
FROM   EnterpriseVaultDirectory.dbo.Archive A,
       EnterpriseVaultDirectory.dbo.Root R,
       yourVaultStoreDB.dbo.ArchivePoint AP,
       yourVaultStoreDB.dbo.Saveset S,
       yourVaultStoreDB.dbo.SavesetProperty SP
WHERE  S.SavesetIdentity = SP.SavesetIdentity
  AND  S.ArchivePointIdentity = AP.ArchivePointIdentity
  AND  AP.ArchivePointId = R.VaultEntryId
  AND  R.RootIdentity = A.RootIdentity
  AND  A.ArchiveName = 'your User'
GROUP BY A.ArchiveName


Remember that if you have users across multiple stores, you will have to run this against each individual vault store, if you want to specify multiple users, do


A.ArchiveName IN ('yourUser1','yourUser2','yourUser3')

 

https://www.linkedin.com/in/alex-allen-turl-07370146

View solution in original post

6 REPLIES 6

JesusWept3
Level 6
Partner Accredited Certified

easiest thing would be to query the ArchivePoint table in the VaultStore database and then link it back to the EnterpriseVaultDirectory database 

SELECT A.ArchiveName, AP.ArchivedItems, AP.ArchivedItemsSize
FROM   EnterpriseVaultDirectory.dbo.Archive A,
       EnterpriseVaultDirectory.dbo.Root R,
       yourVaultStoreDB.dbo.ArchivePoint AP
WHERE  AP.ArchivePointId = R.VaultEntryId
  AND  R.RootIdentity = A.RootIdentity
  AND  A.ArchiveName = 'your User'
https://www.linkedin.com/in/alex-allen-turl-07370146

JesusWept3
Level 6
Partner Accredited Certified

A slower, slightly more silly query would be the following (but might be more accurate and what you're looking for

 

SELECT A.ArchiveName, 
       COUNT(S.IdTransaction) "Num. Items Archived",
       SUM(S.ItemSize)/1024 "Archived Items Size (MB)",
       SUM(SP.OriginalSize)/1024/1024 "Original Email Size (MB)"
FROM   EnterpriseVaultDirectory.dbo.Archive A,
       EnterpriseVaultDirectory.dbo.Root R,
       yourVaultStoreDB.dbo.ArchivePoint AP,
       yourVaultStoreDB.dbo.Saveset S,
       yourVaultStoreDB.dbo.SavesetProperty SP
WHERE  S.SavesetIdentity = SP.SavesetIdentity
  AND  S.ArchivePointIdentity = AP.ArchivePointIdentity
  AND  AP.ArchivePointId = R.VaultEntryId
  AND  R.RootIdentity = A.RootIdentity
  AND  A.ArchiveName = 'your User'
GROUP BY A.ArchiveName


Remember that if you have users across multiple stores, you will have to run this against each individual vault store, if you want to specify multiple users, do


A.ArchiveName IN ('yourUser1','yourUser2','yourUser3')

 

https://www.linkedin.com/in/alex-allen-turl-07370146

View solution in original post

GertjanA
Level 6
Partner    VIP    Accredited Certified

Thanks.

Now, how would I go about if the directorydb is stored on SQL1, and the mailboxdb's are on SQL2?

These are seperate servers.

Thanks.

 

Regards. Gertjan

Liam_Finn1
Level 6
Employee Accredited Certified

You just set them up as linked servers and then fully qualify the names in the form ofLinkName.DatabaseName.SchemaName(dbo).TableName

 

Prone2Typos
Level 6
Partner    VIP    Accredited Certified

you will be publicly flogged for attempting to revitalize a decade old thread with such a question. The simple answer is yes.