SQL Query to get Archive size

[GertjanA]

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

[JesusWept3]

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 · Accepted solution]

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]

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.

[Liam_Finn1]

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

[Prone2Typos]

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