-
Titre
How to estimate a Mailbox Size or Mailbox Message Count in the Archive Manager Database ? -
Description
How to estimate archive mailbox size or how many messages are linked to each mailbox?
-
Résolution
--This query will give you information on the size of mailboxes
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
--This query will give you information on the size attachments per mailboxes
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
--This Query will give you some information on the number and sizes of messages in the 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
--This Query will give you some information on the number and size of attachments stored.
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
-- This query will give you the amount of messages linked to each mailbox. Note: an instance of a Message/Attachment are only in the database once and one message might be linked to multiple mailboxes:
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;