Archive Shuttle 11.5 - Sizing Tool Guide

QAM

The following queries can be used for Quest Archive Manager. All query output units are in MB.

Overall statistics

;WITH archives_CTE AS (
               SELECT [Name], [MailBoxID], [MailBoxType], [ActiveDirectoryExchangeLegacyDN]
   FROM [dbo].[MailBox]
),
archiveStats_CTE AS (
               SELECT COUNT(*) AS [CountOfItems], SUM(CONVERT(BigInt,[t3].[Size])) / 1024 / 1024 AS [SizeInMb], [t2].[MailBoxID]
               FROM [MessageFolder] AS [t0]
               INNER JOIN [MessageData] AS [t1] ON [t0].[MessageID] = [t1].[MessageID]
               INNER JOIN [Folder] AS [t2] ON [t0].[FolderID] = [t2].[FolderID]
               INNER JOIN [Message] AS [t3] ON [t3].[MessageID] = [t1].[MessageID]
               WHERE t2.MailBoxID IN (SELECT archives_CTE.MailBoxID FROM archives_CTE)
               GROUP BY [t2].[MailBoxID]
),
journalStats_CTE AS (
               SELECT COUNT(*) AS [CountOfItems], SUM(CONVERT(BigInt, ISNULL([t1].[Size], 0))) / 1024 / 1024 AS [SizeInMb], '-1' AS [MailboxID]
               FROM [MessageJournalReport] AS [t0]
               INNER JOIN [Message] AS [t1] ON [t0].[MessageID] = [t1].[MessageID]
),
statsPerArchive_CTE AS (
SELECT a.*, ISNULL(ast.CountOfItems, 0) AS [CountOfItems], ISNULL(ast.SizeInMb, 0) AS [SizeInMb] FROM archives_CTE a
LEFT JOIN archiveStats_CTE ast ON ast.MailBoxID = a.MailBoxID
UNION
SELECT 'Journal' AS [Name], '-1' AS [MailboxId], 'Journal' AS [MailboxType], NULL AS [ActiveDirectoryExchangeLegacyDN],
               ISNULL(jst.CountOfItems, 0) AS [CountOfItems], ISNULL(jst.SizeInMb, 0) AS [SizeInMb]
FROM journalStats_CTE jst
)
SELECT SUM(statsPerArchive_CTE.CountOfItems) AS TotalCount, SUM(statsPerArchive_CTE.SizeInMb) AS OveralSize  FROM statsPerArchive_CTE

The SQL Script can be adjusted with date filter to obtain overal statistics for specific time range.

DECLARE @msgDateFrom DATETIME = '01.01.2018';
DECLARE @msgDateTo DATETIME = '12.31.2020';
;WITH archives_CTE AS (
               SELECT [Name], [MailBoxID], [MailBoxType], [ActiveDirectoryExchangeLegacyDN]
   FROM [dbo].[MailBox]
),
archiveStats_CTE AS (
               SELECT COUNT(*) AS [CountOfItems], SUM(CONVERT(BigInt,[t3].[Size])) / 1024 / 1024 AS [SizeInMb], [t2].[MailBoxID]
               FROM [MessageFolder] AS [t0]
               INNER JOIN [MessageData] AS [t1] ON [t0].[MessageID] = [t1].[MessageID]
               INNER JOIN [Folder] AS [t2] ON [t0].[FolderID] = [t2].[FolderID]
               INNER JOIN [Message] AS [t3] ON [t3].[MessageID] = [t1].[MessageID]
               WHERE t2.MailBoxID IN (SELECT archives_CTE.MailBoxID FROM archives_CTE)
               AND t3.MessageDate >= @msgDateFrom AND t3.MessageDate <= @msgDateTo GROUP BY [t2].[MailBoxID] ), journalStats_CTE AS ( SELECT COUNT(*) AS [CountOfItems], SUM(CONVERT(BigInt, ISNULL([t1].[Size], 0))) / 1024 / 1024 AS [SizeInMb], '-1' AS [MailboxID] FROM [MessageJournalReport] AS [t0] INNER JOIN [Message] AS [t1] ON [t0].[MessageID] = [t1].[MessageID] where t1.MessageDate >= @msgDateFrom AND t1.MessageDate <= @msgDateTo
),
statsPerArchive_CTE AS (
SELECT a.*, ISNULL(ast.CountOfItems, 0) AS [CountOfItems], ISNULL(ast.SizeInMb, 0) AS [SizeInMb] FROM archives_CTE a
LEFT JOIN archiveStats_CTE ast ON ast.MailBoxID = a.MailBoxID
UNION
SELECT 'Journal' AS [Name], '-1' AS [MailboxId], 'Journal' AS [MailboxType], NULL AS [ActiveDirectoryExchangeLegacyDN],
               ISNULL(jst.CountOfItems, 0) AS [CountOfItems], ISNULL(jst.SizeInMb, 0) AS [SizeInMb]
FROM journalStats_CTE jst
)
SELECT SUM(statsPerArchive_CTE.CountOfItems) AS TotalCount, SUM(statsPerArchive_CTE.SizeInMb) AS OveralSize  FROM statsPerArchive_CTE

Statistics per archive

;WITH archives_CTE AS (
               SELECT [Name], [MailBoxID], [MailBoxType], [ActiveDirectoryExchangeLegacyDN]
   FROM [dbo].[MailBox]
),
archiveStats_CTE AS (
               SELECT COUNT(*) AS [CountOfItems], SUM(CONVERT(BigInt,[t3].[Size])) / 1024 / 1024 AS [SizeInMb], [t2].[MailBoxID]
               FROM [MessageFolder] AS [t0]
               INNER JOIN [MessageData] AS [t1] ON [t0].[MessageID] = [t1].[MessageID]
               INNER JOIN [Folder] AS [t2] ON [t0].[FolderID] = [t2].[FolderID]
               INNER JOIN [Message] AS [t3] ON [t3].[MessageID] = [t1].[MessageID]
               WHERE t2.MailBoxID IN (SELECT archives_CTE.MailBoxID FROM archives_CTE)
               GROUP BY [t2].[MailBoxID]
),
journalStats_CTE AS (
               SELECT COUNT(*) AS [CountOfItems], SUM(CONVERT(BigInt, ISNULL([t1].[Size], 0))) / 1024 / 1024 AS [SizeInMb], '-1' AS [MailboxID]
               FROM [MessageJournalReport] AS [t0]
               INNER JOIN [Message] AS [t1] ON [t0].[MessageID] = [t1].[MessageID]
),
statsPerArchive_CTE AS (
SELECT a.*, ISNULL(ast.CountOfItems, 0) AS [CountOfItems], ISNULL(ast.SizeInMb, 0) AS [SizeInMb] FROM archives_CTE a
LEFT JOIN archiveStats_CTE ast ON ast.MailBoxID = a.MailBoxID
UNION
SELECT 'Journal' AS [Name], '-1' AS [MailboxId], 'Journal' AS [MailboxType], NULL AS [ActiveDirectoryExchangeLegacyDN],
               ISNULL(jst.CountOfItems, 0) AS [CountOfItems], ISNULL(jst.SizeInMb, 0) AS [SizeInMb]
FROM journalStats_CTE jst
)
SELECT * FROM statsPerArchive_CTE
--WHERE statsPerArchive_CTE.MailBoxType = 'Journal'

If you need to get statistics for specific MailBoxType, then remove comment characters ‘- -‘ from above SQL script and change the MailboxType in the where condition. Supported MailboxTypes are following:

·'User'

·'Virtual'

·'PST'

·'Journal'

The SQL Script above can be adjusted with date filter to obtain statistics for specific time range. Also specific MailBoxType can be used using where condition as it is described above.

DECLARE @msgDateFrom DATETIME = '01.01.2010';
DECLARE @msgDateTo DATETIME = '12.31.2020';
;WITH archives_CTE AS (
               SELECT [Name], [MailBoxID], [MailBoxType], [ActiveDirectoryExchangeLegacyDN]
   FROM [dbo].[MailBox]
),
archiveStats_CTE AS (
               SELECT COUNT(*) AS [CountOfItems], SUM(CONVERT(BigInt,[t3].[Size])) / 1024 / 1024 AS [SizeInMb], [t2].[MailBoxID]
               FROM [MessageFolder] AS [t0]
               INNER JOIN [MessageData] AS [t1] ON [t0].[MessageID] = [t1].[MessageID]
               INNER JOIN [Folder] AS [t2] ON [t0].[FolderID] = [t2].[FolderID]
               INNER JOIN [Message] AS [t3] ON [t3].[MessageID] = [t1].[MessageID]
               WHERE t2.MailBoxID IN (SELECT archives_CTE.MailBoxID FROM archives_CTE)
               AND t3.MessageDate >= @msgDateFrom AND t3.MessageDate <= @msgDateTo GROUP BY [t2].[MailBoxID] ), journalStats_CTE AS ( SELECT COUNT(*) AS [CountOfItems], SUM(CONVERT(BigInt, ISNULL([t1].[Size], 0))) / 1024 / 1024 AS [SizeInMb], '-1' AS [MailboxID] FROM [MessageJournalReport] AS [t0] INNER JOIN [Message] AS [t1] ON [t0].[MessageID] = [t1].[MessageID] where t1.MessageDate >= @msgDateFrom AND t1.MessageDate <= @msgDateTo
),
statsPerArchive_CTE AS (
SELECT a.*, ISNULL(ast.CountOfItems, 0) AS [CountOfItems], ISNULL(ast.SizeInMb, 0) AS [SizeInMb] FROM archives_CTE a
LEFT JOIN archiveStats_CTE ast ON ast.MailBoxID = a.MailBoxID
UNION
SELECT 'Journal' AS [Name], '-1' AS [MailboxId], 'Journal' AS [MailboxType], NULL AS [ActiveDirectoryExchangeLegacyDN],
               ISNULL(jst.CountOfItems, 0) AS [CountOfItems], ISNULL(jst.SizeInMb, 0) AS [SizeInMb]
FROM journalStats_CTE jst
)
SELECT * FROM statsPerArchive_CTE
--WHERE statsPerArchive_CTE.MailBoxType = 'Journal'

Size of individual archive

When ingesting data into a target Exchange or Office 365 archive mailbox, quotas may stop the ingest of items. This section describes how to find the size of a single archive in Quest Archive Manager.

--Get items for QAM mailbox

DECLARE @mailboxId INT = --your mailbox id

SELECT [t4].[MailBoxID], [t4].[value] AS [Count], [t4].[value2] AS [Sum]

FROM (

   SELECT COUNT(*) AS [value], SUM(CONVERT(BigInt,[t3].[Size])) AS [value2], [t2].[MailBoxID]

   FROM [MessageFolder] AS [t0]

   INNER JOIN [MessageData] AS [t1] ON [t0].[MessageID] = [t1].[MessageID]

   INNER JOIN [Folder] AS [t2] ON [t0].[FolderID] = [t2].[FolderID]

   INNER JOIN [Message] AS [t3] ON [t3].[MessageID] = [t1].[MessageID]

   GROUP BY [t2].[MailBoxID]

   ) AS [t4]

WHERE [t4].[MailBoxID] IN (@mailboxId)