Forum Discussion

modemis's avatar
modemis
Level 3
12 years ago
Solved

Need a SQL script to determine how much compressed and uncompressed size of all archives.

Hello all.

 

Can anyone help with a request?  I need a SQL query to give me a total of compressed and uncompressed data that resides in vault stores.  The customer wants to find out how big the data will grow if they migrate all to PST files.

Thank you ahead of time.

 

Melih

Enterprise Vault
Information Governance
Reporting
Tip-How to
Windows Server (2003-2008)
  • JesusWept3's avatar
    JesusWept3
    12 years ago

    This should work for you, just replace EVVSYourVaultStore_1 with the name of your Vault Store Database

     

    SELECT A.ArchiveName "Archive Name",
       SUM(S.ItemSize)/1024 "Compressed Size (MB)",
       SUM(SP.OriginalSize)/1024/1024 "Uncompressed Size (MB)"
FROM   EnterpriseVaultDirectory.dbo.Archive A,
       EnterpriseVaultDirectory.dbo.Root R,
       EVVSYourVaultStore_1.dbo.ArchivePoint AP,
       EVVSYourVaultStore_1.dbo.Saveset S,
       EVVSYourVaultStore_1.dbo.SavesetProperty SP
WHERE  A.RootIdentity = R.RootIdentity
  AND  R.VaultEntryId = AP.ArchivePointId
  AND  AP.ArchivePointIdentity = S.ArchivePointIdentity
  AND  S.SavesetIdentity = SP.SavesetIdentity
GROUP BY A.ArchiveName
ORDER BY A.ArchiveName

10 Replies

  • JesusWept3's avatar
    JesusWept3
    Level 6
    12 years ago

    This should work for you, just replace EVVSYourVaultStore_1 with the name of your Vault Store Database

     

    SELECT A.ArchiveName "Archive Name",
       SUM(S.ItemSize)/1024 "Compressed Size (MB)",
       SUM(SP.OriginalSize)/1024/1024 "Uncompressed Size (MB)"
FROM   EnterpriseVaultDirectory.dbo.Archive A,
       EnterpriseVaultDirectory.dbo.Root R,
       EVVSYourVaultStore_1.dbo.ArchivePoint AP,
       EVVSYourVaultStore_1.dbo.Saveset S,
       EVVSYourVaultStore_1.dbo.SavesetProperty SP
WHERE  A.RootIdentity = R.RootIdentity
  AND  R.VaultEntryId = AP.ArchivePointId
  AND  AP.ArchivePointIdentity = S.ArchivePointIdentity
  AND  S.SavesetIdentity = SP.SavesetIdentity
GROUP BY A.ArchiveName
ORDER BY A.ArchiveName
  • John_Santana's avatar
    John_Santana
    Level 6
    12 years ago

    Thanks for sharing it here mate !

    is it possible to differentiate the data that is Exchange Mailbox vs. the FSA carchived files ?

  • JesusWept3's avatar
    JesusWept3
    Level 6
    12 years ago
    Yeah, just put in the following in the WHERE clause AND R.ArchiveType=9
  • John_Santana's avatar
    John_Santana
    Level 6
    12 years ago

    ok, I've finally made it working by using this script below:

    it is R.type = 9 :-) thanks for the pointer Jesus.

    SELECT A.archivename                      "Archive Name", 
       Sum(S.itemsize) / 1024             "Compressed Size (MB)", 
       Sum(SP.originalsize) / 1024 / 1024 "Uncompressed Size (MB)" 
FROM   enterprisevaultdirectory.dbo.archive A, 
       enterprisevaultdirectory.dbo.root R, 
       EVVaultstore1.dbo.archivepoint AP, 
       EVVaultstore1.dbo.saveset S, 
       EVVaultstore1.dbo.savesetproperty SP 
WHERE  A.rootidentity = R.rootidentity 
       AND R.vaultentryid = AP.archivepointid 
       AND AP.archivepointidentity = S.archivepointidentity 
       AND S.savesetidentity = SP.savesetidentity 
       AND R.type = 9 
GROUP  BY A.archivename 
ORDER  BY A.archivename
  • John_Santana's avatar
    John_Santana
    Level 6
    12 years ago

    Thanks for the reply Jesus,

    What's the type code forthe FSA / file archive ?

    9 is for Exchange Archive, I need the FSA code if available.

  • modemis's avatar
    modemis
    Level 3
    12 years ago

    Thank you Jesus.

    However, when I run the query on the MBX Vault store I get a result of 37TB compressed and 42TB uncompressed but when I check the disk size, I only see 4TB worth of DVS files on all drives in total.

    Am I doing something wrong?

    Thanks again.

  • John_Santana's avatar
    John_Santana
    Level 6
    12 years ago

    Modemis,

    The uncompressed size is the total disk space usage on the target (Exchnage or File server), while the compressed size is the save set disk usage in EV VaultStore.

  • Merv's avatar
    Merv
    Level 6
    12 years ago

    FYI Archive Types:

    Exchange Journal - 17 
    Exchange Mailbox -  9
    FSA  -  129
    Shared Archive -  5
    Domino Journal -   513
    Domino Mailbox -  1025

  • Merv's avatar
    Merv
    Level 6
    12 years ago

    from the help file on the vault admin console

    An archive can be used by many users. For each user who needs to use an archive, you have to grant access to that archive. An individual user may have access to many archives.

    Every user also has a personal archive that is not shared with any other users. Those users who have access to a shared archive can make Enterprise Vault send specific folders to the shared archive, rather than to the personal archive.

    BTW Public Folder - 33