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

[modemis]

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

[JesusWept3 · Accepted solution]

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]

Thanks for sharing it here mate !

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

[JesusWept3]

Yeah, just put in the following in the WHERE clause AND R.ArchiveType=9

[John_Santana]

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]

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]

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]

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]

FYI Archive Types:

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

[John_Santana]

Merv,

What is shared archive used for / by ?

[Merv]

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