Video Screencast Help

SQL Query to get Archive size

Created: 11 Aug 2011 • Updated: 01 Sep 2011 | 4 comments
GertjanA's picture
This issue has been solved. See solution.

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?



Discussion Filed Under:

Comments 4 CommentsJump to latest comment

JesusWept3's picture

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'
JesusWept3's picture

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')

GertjanA's picture


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

These are seperate servers.


Thank you, Gertjan, MCSE, MCITP,MCTS, SCS, STS

Liam Finn's picture

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