Archive Shuttle 11.5 - SQL Best Practices

Get stats per archive with date filter

Use MAMEXCH --(If the database name is not MAMEXCH change that here)

SELECT em.DISPLAYNAME,

      CAST(COUNT(ed.DOCSEQID) AS BIGINT) AS CountOfArchivedItems,

      SUM(CAST(ed.ARCHIVE_SIZE AS BIGINT))/1024/1024 AS TotalCompressedSizeInMB,

      SUM(CAST(ed.UNCOMPRESSED_SIZE AS BIGINT)) /1024/1024 AS TotalUncompressedInMB

FROM [EXCHANGEPAMMAILBOXES] em

   LEFT JOIN [EXCHANGEPAMDOCUMENTS] ed ON ed.MBXID = em.MBXID

WHERE em.MBXID > 0

AND ed.RECEIVED Between '2016-03-01' and '2021-07-31'

GROUP BY em.DISPLAYNAME

Result:

Get stats per archive with date filter and minimum and maximum ReceivedDate

Use MAMEXCH --(If the database name is not MAMEXCH change that here)

SELECT em.DISPLAYNAME,

      CAST(COUNT(ed.DOCSEQID) AS BIGINT) AS CountOfArchivedItems,

      SUM(CAST(ed.ARCHIVE_SIZE AS BIGINT))/1024/1024 AS TotalCompressedSizeInMB,

      SUM(CAST(ed.UNCOMPRESSED_SIZE AS BIGINT)) /1024/1024 AS TotalUncompressedInMB,

      Min(ed.RECEIVED) AS OldestReceivedDate,

      Max(ed.RECEIVED) AS YoungestReceivedDate

FROM [EXCHANGEPAMMAILBOXES] em

   LEFT JOIN [EXCHANGEPAMDOCUMENTS] ed ON ed.MBXID = em.MBXID

WHERE em.MBXID > 0

AND ed.RECEIVED Between '2016-03-01' and '2021-07-31'

GROUP BY em.DISPLAYNAME

Result:

EAS

The following queries can be used for EAS. All query output units are in MB.

Overall statistics

-- total size

SELECT COUNT(t3.MSGID) AS ItemsCount, (SUM(t3.MSGSIZE) /1024 / 1024) as ItemsSizeMb

               FROM USERS AS t0

               INNER JOIN REFER AS t1 ON(t0.USERID) = t1.USERID

               INNER JOIN FOLDER AS t2 ON t1.FOLDERID = t2.FOLDERID

               INNER JOIN PROFILE AS t3 ON t1.MSGID = t3.MSGID

               WHERE(t1.MSGID >= 0) AND(t1.FOLDERID >= 0)

               -- enable condition below in case you want to get stats for enabled/disabled archives

               -- 0 = enabled

               -- 1 = disabled

               --and t0.EASSTATUS = 0

Result: