This guide lists SQL sizing queries for Metalogix, EAS, SourceOne, EMX, Enterprise Vault Discovery Accelerator, QAM and Enterprise Vault in Archive Shuttle.
The following are the sizing queries size column description for each source in the DB. Each column will note if the output is the compressed or uncompressed size.
License calculations are based from the compressed sizes in the AS DB (table Item, column CompressedSize).
Metalogix
·EXCHANGEPAMDOCUMENTS.ARCHIVE_SIZE used as compressed size.
·EXCHANGEPAMDOCUMENTS.UNCOMPRESSED_SIZE used as uncompressed size.
EAS
·Table PROFILE, column MSGSIZE - size from EAS DB used as compressed and uncompressed sizes in AS DB.
SourceOne/EMX
·Table MESSAGE, column MSGSIZE - size from S1/EMX DB used as compressed and uncompressed sizes in AS DB.
EVDA
·View view_DiscoveredItems, column SIZE - size from EVDA DB used as compressed and uncompressed sizes in AS DB.
QAM
·Table MESSAGE, column SIZE - size from QAM DB used as compressed and uncompressed sizes in AS DB.
EV
·View view_ListVaults, column ArchivedItemsSize returns compressed data.
The following queries can be used for Enterprise Vault. All query output units are in MB.
|
|
NOTE: Query outputs utilize compressed values to determine licensing. |
|
|
NOTE: This query should be run against the Enterprise Vault Vault Store Database. |
-- compressed size - SaveSet.ItemSize - Default size is in KiloBytes
-- uncompressed size - SavesetProperty.OriginalSize - Default size is in Bytes
-- Run this query over EV Item database
-- overall statistics
SELECT COUNT(*) AS TotalNumberOfArchives, SUM(tmp.CountOfarchivedItems) AS TotalCountOfItems, SUM(tmp.TotalCompressedSizeMB) AS TotalCompressedSizeMB,
SUM(tmp.TotalUncompressedSizeMB) AS TotalUncompressedSizeMB
FROM (
SELECT av.ArchiveName, av.ArchiveTypeName, ve.VaultStoreName, COUNT(s.SavesetIdentity) AS CountOfarchivedItems, SUM(CONVERT(BIGINT, s.ItemSize)) / 1024 AS TotalCompressedSizeMB,
SUM(CONVERT(BIGINT, sp.OriginalSize)) / 1024 / 1024 AS TotalUncompressedSizeMB
FROM dbo.ArchivePoint ap WITH(NOLOCK)
INNER JOIN EnterpriseVaultDirectory.dbo.ArchiveView av ON ap.ArchivePointId = av.VaultEntryId
INNER JOIN EnterpriseVaultDirectory.dbo.VaultStoreEntry ve ON av.VaultStoreEntryId = ve.VaultStoreEntryId
LEFT JOIN dbo.Saveset s ON s.ArchivePointIdentity = ap.ArchivePointIdentity
LEFT JOIN dbo.SavesetProperty sp ON s.SavesetIdentity = sp.SavesetIdentity
-- Enable row in case you want stats per single vault store
-- WHERE ve.VaultStoreName LIKE '%EV14VSG01VS01%'
GROUP BY av.ArchiveName, av.ArchiveTypeName, ve.VaultStoreName
) AS tmp
Result:
