Archive Shuttle 11.5 - SQL Best Practices

Define number and size of attachments

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

SELECT

YEAR(Message.DateProcessed) AS Year,

MONTH(Message.DateProcessed) AS Month,

COUNT(*) AS AttachmentCount,

SUM(Cast(Attachmentfile.Size as BIGINT))/1024/1024 as AttachmentTotalMB

FROM AttachmentFile JOIN Attachment ON AttachmentFile.AttachmentFileID = Attachment.AttachmentFileID

JOIN Message ON Attachment.MessageID = Message.MessageID

GROUP BY YEAR(Message.DateProcessed), MONTH(Message.DateProcessed)

ORDER BY 1, 2,3,4

Define number of messages linked to each mailbox

Run this SQL query to determine the number of messages linked to each mailbox in Quest Archive Manager.

USE ArchiveManager;

SELECT MailBox.MailBoxID, MailBox.Name As MailboxUserName, COUNT(MailBoxMessage.MessageID) As TotalMessageItems FROM MailBox, MailBoxMessage WHERE MailBox.MailBoxID = MailBoxMessage.MailBoxID GROUP BY Mailbox.MailBoxID, Mailbox.Name ORDER BY MailBoxID;