Archive Shuttle 11.5 - SQL Best Practices

Define size in uncompressed format

Run this SQL query to determine the amount of uncompressed data in Quest Archive Manager.

Use ArchiveManager

SELECT COUNT_BIG(distinct MailBox.MailBoxID) AS [No Archives], COUNT_BIG(MailBoxMessage.MessageID) AS [No Messages], ROUND(SUM(CAST(Message.Size AS REAL)) / 1024 / 1024, 2) [Total Size (MB)]

FROM MailBox INNER JOIN

MailBoxMessage ON MailBox.MailBoxID = MailBoxMessage.MailBoxID INNER JOIN

Message ON MailBoxMessage.MessageID = Message.MessageID

Define size of mailboxes

Run this SQL query to determine the size of mailboxes in Quest Archive Manager.

Use ArchiveManager

Select MA.mailboxID, MA.Name, SUM(Cast(ME.Size as BIGINT))/1024/1024 as MailboxSizeMB

From MailBox MA Join MailboxMessage MM On MA.MailBoxID = MM.MailBoxID

Join Message ME on MM.MessageID = ME.MessageID

–Uncomment to filter

–Where

–MA.MailBoxID = 1

–AND ME.DateProcessed Between ‘2016-09-09’ and ‘2017-01-01’

GROUP BY MA.MailBoxID, MA.Name

Define size of attachments per mailbox

Run this SQL query to determine the size of attachments per mailbox in Quest Archive Manager.

Use ArchiveManager

Select MA.mailboxID, MA.Name, SUM(Cast(AF.Size as BIGINT))/1024/1024 as AttachementSizeMB

From AttachmentFile AF Join Attachment AT On AF.AttachmentFileID = AT.AttachmentFileID

Join Message ME on ME.MessageID = AT.MessageID

Join MailboxMessage MM on ME.MessageID = MM.MessageID

Join Mailbox MA on MM.MailBoxID = MA.MailBoxID

–Uncomment to filter

–Where

–MA.MailBoxID = 1

–AND ME.DateProcessed Between ‘2016-09-09’ and ‘2017-01-01’

Group BY MA.MailBoxID, MA.Name

Define number and sizes of messages in the database

Run this SQL query to determine the number and sizes of messages in the Quest Archive Manager database.

SELECT

YEAR(DateProcessed) AS Year,

MONTH(DateProcessed) AS Month,

COUNT(*) AS MessageCount,

SUM(Cast(Size as BIGINT))/1024/1024 as MailboxSizeMB

FROM Message

GROUP BY YEAR(DateProcessed),

MONTH(DateProcessed)

ORDER BY 1, 2,3,4