Forum Discussion

GertjanA's avatar
GertjanA
Moderator
14 years ago
Solved

SQL Query to get Archive size

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

Enterprise Vault
Information Governance
  • JesusWept3's avatar
    JesusWept3
    14 years ago

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

     

6 Replies

  • JesusWept3's avatar
    JesusWept3
    Level 6
    14 years ago

    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 avatar
    JesusWept3
    Level 6
    14 years ago

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

     

      • Prone2Typos's avatar
        Prone2Typos
        Moderator
        5 years ago

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

  • GertjanA's avatar
    GertjanA
    Moderator
    14 years ago

    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's avatar
    Liam_Finn1
    Level 6
    14 years ago

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