Archive Shuttle 11.5 - Sizing Tool Guide

Overall statistics

-- drop tables if still exists

 

IF OBJECT_ID('dbo.ArchiveEmailIds') IS NOT NULL DROP TABLE ArchiveEmailIds

 

IF OBJECT_ID('dbo.ExchangeArchiveStats') IS NOT NULL DROP TABLE ExchangeArchiveStats

 

-- Get all Exchange archive stats in batches

 

-- @count variable defines number of archive for which we get stats in one batch.

 

DECLARE @ErrorMessage NVARCHAR(4000);

 

DECLARE @log AS NVARCHAR(4000);

 

DECLARE @journal BIGINT = -3515994502517958093;

 

DECLARE @pstFileType INT = 4;

 

DECLARE @smtpFileType INT = 3;

 

DECLARE @exchangeArchiveType INT = 1;

 

DECLARE @count INT = 10000;

 

DECLARE @start INT = 0;

 

DECLARE @total INT = 0;

 

DECLARE @affectedRowsCount INT = 1;

 

 

 

-- create tables used to store stats

 

CREATE TABLE ArchiveEmailIds (

 

  EmailId BIGINT NOT NULL

 

)

 

CREATE TABLE ExchangeArchiveStats (

 

  [EmailId] BIGINT NOT NULL,

 

  [Count] BIGINT NULL,

 

  [Total Size MB] BIGINT NULL

 

)

 

 

 

-- get exchange archive stats

 

SET @log = 'Started getting stats for Exchange archives.';

 

RAISERROR(@log, 0, 0) WITH NOWAIT;

 

 

 

BEGIN TRY

 

  WHILE (@affectedRowsCount > 0)

 

  BEGIN

 

      TRUNCATE TABLE ArchiveEmailIds

 

 

 

      ;WITH CTE_RouteMasks AS

 

      (

 

          SELECT r.EmailId, COUNT(1) AS RouteCount FROM dbo.Route r

 

          WHERE ([r].[RouteMask] & 4 = 4)

 

          GROUP BY r.EmailId

 

      )

 

      INSERT INTO ArchiveEmailIds

 

      SELECT TOP(@count) [x].[EmailId]

 

      FROM (

 

          SELECT ROW_NUMBER() OVER (ORDER BY [ea].[EmailId]) AS [ROW_NUMBER], [ea].[EmailId], [ea].[EmailAddress], [ea].[Type]

 

          FROM [EmailAddress] AS [ea]

 

          WHERE (ea.Type = @exchangeArchiveType OR ea.Type IS NULL) AND EXISTS(

 

              SELECT NULL AS [EMPTY]

 

              FROM CTE_RouteMasks AS [r]

 

              WHERE [r].[EmailId] = [ea].[EmailId]

 

              )

 

          ) AS [x]

 

      WHERE [x].[ROW_NUMBER] > @start

 

      ORDER BY [x].[ROW_NUMBER]

 

 

 

      INSERT INTO ExchangeArchiveStats

 

      SELECT r.EmailId, COUNT_BIG(DISTINCT(r.[MessageID])) as [Count], ROUND(SUM(CAST(m.MsgSize AS REAL)) / 1024 / 1024, 2) AS [Total Size MB]

 

      FROM Route r

 

          INNER JOIN ArchiveEmailIds tmp ON tmp.EmailId = r.EmailId

 

          INNER JOIN dbo.FolderMessage fm ON r.MessageID = fm.MessageID AND r.FolderId = fm.FolderId and r.PartitionId = fm.PartitionId

 

          INNER JOIN dbo.Message m ON m.MessageID = r.MessageID AND m.PartitionId = r.PartitionId

 

          INNER JOIN dbo.Volume v ON v.VolumeId = fm.VolumeId

 

      WHERE r.RouteMask & 4 = 4

 

      group by r.[EmailId];

 

 

 

      SET @affectedRowsCount = @@ROWCOUNT;

 

      SET @start = @start + @count;

 

      SET @total = @total + @affectedRowsCount;

 

      SET @log = 'Got stats for ' + CAST(@affectedRowsCount AS CHAR(10)) + ' archives';

 

 

 

      RAISERROR(@log, 0, 0) WITH NOWAIT;

 

  END

 

 

 

  --drop temp tables

 

  DROP TABLE ArchiveEmailIds

 

END TRY

 

BEGIN CATCH

 

  DROP TABLE ArchiveEmailIds

 

  DROP TABLE ExchangeArchiveStats

 

  SELECT

 

  @ErrorMessage=ERROR_MESSAGE();

 

  RAISERROR('Error while getting exchange archives. Error: %s', 15, 1, @ErrorMessage)

 

END CATCH

 

 

 

SET @log = 'Finished getting stats for Exchange archives. Got total ' + CAST(@total AS CHAR(10)) + ' archives';

 

RAISERROR(@log, 0, 0) WITH NOWAIT;

 

SET @log = 'Started getting overal stats';

 

RAISERROR(@log, 0, 0) WITH NOWAIT;

 

-- get pst and journal stats

 

-- show all the stats results

 

 

 

;WITH journalAndPstStats_CTE AS (

 

  SELECT r.EmailId, COUNT_BIG(DISTINCT(r.[MessageID])) as [Count], ROUND(SUM(CAST(m.MsgSize AS REAL)) / 1024 / 1024, 2) AS [Total Size MB]

 

  FROM dbo.EmailAddress ea

 

      INNER JOIN dbo.Route r ON r.EmailId = ea.EmailId AND (ea.EmailId = @journal OR ea.Type = @pstFileType)

 

      INNER JOIN dbo.FolderMessage fm ON r.MessageID = fm.MessageID AND r.FolderId = fm.FolderId and r.PartitionId = fm.PartitionId

 

      INNER JOIN dbo.Message m ON m.MessageID = r.MessageID AND m.PartitionId = r.PartitionId

 

      INNER JOIN dbo.Volume v ON v.VolumeId = fm.VolumeId

 

  group by r.[EmailId]

 

)

 

-- !!! Enable this query only in case SMTP stats should be shown !!!

-- , statsSmtpArchive_CTE AS (

--

--    SELECT r.EmailId, COUNT_BIG(DISTINCT(r.[MessageID])) as [Count], ROUND(SUM(CAST(m.MsgSize AS REAL)) / 1024 / 1024, 2) AS [Total Size MB]

--

--    FROM dbo.EmailAddress ea

--

--        INNER JOIN dbo.Route r ON r.EmailId = ea.EmailId AND ea.Type = @smtpFileType

--

--        INNER JOIN dbo.FolderMessage fm ON r.MessageID = fm.MessageID AND r.FolderId = fm.FolderId and r.PartitionId = fm.PartitionId

--

--        INNER JOIN dbo.Message m ON m.MessageID = r.MessageID AND m.PartitionId = r.PartitionId

--

--        INNER JOIN dbo.Volume v ON v.VolumeId = fm.VolumeId

--

--    group by r.[EmailId]

--

-- )

 

, statsPerArchive_CTE AS (

 

  SELECT ExchangeStats.EmailId, ExchangeStats.Count, ExchangeStats.[Total Size MB]  

 

  FROM ExchangeArchiveStats AS ExchangeStats

 

  UNION

 

  SELECT JournaAndPstStats.EmailId, JournaAndPstStats.Count, JournaAndPstStats.[Total Size MB]

 

  FROM journalAndPstStats_CTE AS JournaAndPstStats

 

  -- !!! Enable this UNION only in case SMTP stats should be shown !!!

 

  --UNION

 

  --SELECT stats.EmailId, stats.Count, stats.[Total Size MB]

 

  --FROM statsSmtpArchive_CTE stats

 

  ---- use statement below to define domain list you are interested in

 

  ----    INNER JOIN dbo.EmailAddress ea ON ea.EmailId = stats.EmailId

 

  ----WHERE ea.EmailAddress LIKE '%domain1.com%'

 

  ----    OR ea.EmailAddress LIKE '%domain2.com%'

 

)

 

SELECT COUNT(1) AS archivesCount, SUM(statsPerArchive_CTE.Count) AS TotalCount, SUM(statsPerArchive_CTE.[Total Size MB]) AS TotalSizeMb

 

FROM statsPerArchive_CTE

 

 

 

DROP TABLE ExchangeArchiveStats

 

 

 

SET @log = 'Finished getting overal stats';

 

RAISERROR(@log, 0, 0) WITH NOWAIT;

Overall statistics with date filtering

-- drop tables if still exists

 

IF OBJECT_ID('dbo.ArchiveEmailIds') IS NOT NULL DROP TABLE ArchiveEmailIds

 

IF OBJECT_ID('dbo.ExchangeArchiveStats') IS NOT NULL DROP TABLE ExchangeArchiveStats

 

-- Get all Exchange archive stats in batches

 

-- @count variable defines number of archive for which we get stats in one batch.

 

DECLARE @ErrorMessage NVARCHAR(4000);

 

DECLARE @log AS NVARCHAR(4000);

 

DECLARE @journal BIGINT = -3515994502517958093;

 

DECLARE @pstFileType INT = 4;

 

DECLARE @smtpFileType INT = 3;

 

DECLARE @exchangeArchiveType INT = 1;

 

DECLARE @count INT = 10000;

 

DECLARE @start INT = 0;

 

DECLARE @total INT = 0;

 

DECLARE @affectedRowsCount INT = 1;

 

DECLARE @msgDateFrom DATETIME = '01.01.2010';

 

DECLARE @msgDateTo DATETIME = '12.31.2020';

 

 

 

-- create tables used to store stats

 

CREATE TABLE ArchiveEmailIds (

 

  EmailId BIGINT NOT NULL

 

)

 

CREATE TABLE ExchangeArchiveStats (

 

  [EmailId] BIGINT NOT NULL,

 

  [Count] BIGINT NULL,

 

  [Total Size MB] BIGINT NULL

 

)

 

 

 

-- get exchange archive stats

 

SET @log = 'Started getting stats for Exchange archives.';

 

RAISERROR(@log, 0, 0) WITH NOWAIT;

 

 

 

BEGIN TRY

 

  WHILE (@affectedRowsCount > 0)

 

  BEGIN

 

      TRUNCATE TABLE ArchiveEmailIds

 

 

 

      ;WITH CTE_RouteMasks AS

 

      (

 

          SELECT r.EmailId, COUNT(1) AS RouteCount FROM dbo.Route r

 

          WHERE ([r].[RouteMask] & 4 = 4)

 

          GROUP BY r.EmailId

 

      )

 

      INSERT INTO ArchiveEmailIds

 

      SELECT TOP(@count) [x].[EmailId]

 

      FROM (

 

          SELECT ROW_NUMBER() OVER (ORDER BY [ea].[EmailId]) AS [ROW_NUMBER], [ea].[EmailId], [ea].[EmailAddress], [ea].[Type]

 

          FROM [EmailAddress] AS [ea]

 

          WHERE (ea.Type = @exchangeArchiveType OR ea.Type IS NULL) AND EXISTS(

 

              SELECT NULL AS [EMPTY]

 

              FROM CTE_RouteMasks AS [r]

 

              WHERE [r].[EmailId] = [ea].[EmailId]

 

              )

 

          ) AS [x]

 

      WHERE [x].[ROW_NUMBER] > @start

 

      ORDER BY [x].[ROW_NUMBER]

 

 

 

      INSERT INTO ExchangeArchiveStats

 

      SELECT r.EmailId, COUNT_BIG(DISTINCT(r.[MessageID])) as [Count], ROUND(SUM(CAST(m.MsgSize AS REAL)) / 1024 / 1024, 2) AS [Total Size MB]

 

      FROM Route r

 

          INNER JOIN ArchiveEmailIds tmp ON tmp.EmailId = r.EmailId

 

          INNER JOIN dbo.FolderMessage fm ON r.MessageID = fm.MessageID AND r.FolderId = fm.FolderId and r.PartitionId = fm.PartitionId

 

          INNER JOIN dbo.Message m ON m.MessageID = r.MessageID AND m.PartitionId = r.PartitionId

 

          INNER JOIN dbo.Volume v ON v.VolumeId = fm.VolumeId

 

      WHERE r.RouteMask & 4 = 4

 

          -- !!! if you don't want to use date filter remove condition below !!!

 

          AND m.MsgDate >= @msgDateFrom AND m.MsgDate <= @msgDateTo

 

      group by r.[EmailId];

 

 

 

      SET @affectedRowsCount = @@ROWCOUNT;

 

      SET @start = @start + @count;

 

      SET @total = @total + @affectedRowsCount;

 

      SET @log = 'Got stats for ' + CAST(@affectedRowsCount AS CHAR(10)) + ' archives';

 

      RAISERROR(@log, 0, 0) WITH NOWAIT;

 

  END

 

 

 

  --drop temp tables

 

  DROP TABLE ArchiveEmailIds

 

END TRY

 

BEGIN CATCH

 

  DROP TABLE ArchiveEmailIds

 

  DROP TABLE ExchangeArchiveStats

 

  SELECT

 

  @ErrorMessage=ERROR_MESSAGE();

 

  RAISERROR('Error while getting exchange archives. Error: %s', 15, 1, @ErrorMessage)

 

END CATCH

 

 

 

SET @log = 'Finished getting stats for Exchange archives. Got total ' + CAST(@total AS CHAR(10)) + ' archives';

 

RAISERROR(@log, 0, 0) WITH NOWAIT;

 

SET @log = 'Started getting overal stats';

 

RAISERROR(@log, 0, 0) WITH NOWAIT;

 

-- get pst and journal stats

 

-- show all the stats results

 

;WITH journalAndPstStats_CTE AS (

 

  SELECT r.EmailId, COUNT_BIG(DISTINCT(r.[MessageID])) as [Count], ROUND(SUM(CAST(m.MsgSize AS REAL)) / 1024 / 1024, 2) AS [Total Size MB]

 

  FROM dbo.EmailAddress ea

 

      INNER JOIN dbo.Route r ON r.EmailId = ea.EmailId AND (ea.EmailId = @journal OR ea.Type = @pstFileType)

 

      INNER JOIN dbo.FolderMessage fm ON r.MessageID = fm.MessageID AND r.FolderId = fm.FolderId and r.PartitionId = fm.PartitionId

 

      INNER JOIN dbo.Message m ON m.MessageID = r.MessageID AND m.PartitionId = r.PartitionId

 

      INNER JOIN dbo.Volume v ON v.VolumeId = fm.VolumeId

 

  -- !!! if you don't want to use dat filter remove condition below !!!

 

  WHERE m.MsgDate >= @msgDateFrom AND m.MsgDate <= @msgDateTo

 

  group by r.[EmailId]

 

)

-- !!! Enable this query only in case SMTP stats should be shown !!!

-- , statsSmtpArchive_CTE AS (

--

--    SELECT r.EmailId, COUNT_BIG(DISTINCT(r.[MessageID])) as [Count], ROUND(SUM(CAST(m.MsgSize AS REAL)) / 1024 / 1024, 2) AS [Total Size MB]

--

--    FROM dbo.EmailAddress ea

--

--        INNER JOIN dbo.Route r ON r.EmailId = ea.EmailId AND ea.Type = @smtpFileType

--

--        INNER JOIN dbo.FolderMessage fm ON r.MessageID = fm.MessageID AND r.FolderId = fm.FolderId and r.PartitionId = fm.PartitionId

--

--        INNER JOIN dbo.Message m ON m.MessageID = r.MessageID AND m.PartitionId = r.PartitionId

--

--        INNER JOIN dbo.Volume v ON v.VolumeId = fm.VolumeId

--

--    -- !!! if you don't want to use dat filter remove condition below !!!

--

--    WHERE m.MsgDate >= @msgDateFrom AND m.MsgDate <= @msgDateTo

--

--    group by r.[EmailId]

--

-- )

 

, statsPerArchive_CTE AS (

 

  SELECT ExchangeStats.EmailId, ExchangeStats.Count, ExchangeStats.[Total Size MB]  

 

  FROM ExchangeArchiveStats AS ExchangeStats

 

  UNION

 

  SELECT JournaAndPstStats.EmailId, JournaAndPstStats.Count, JournaAndPstStats.[Total Size MB]

 

  FROM journalAndPstStats_CTE AS JournaAndPstStats

 

  -- !!! Enable this UNION only in case SMTP stats should be shown !!!

 

  --UNION

 

  --SELECT stats.EmailId, stats.Count, stats.[Total Size MB]

 

  --FROM statsSmtpArchive_CTE stats

 

  ---- use statement below to define domain list you are interested in

 

  ----    INNER JOIN dbo.EmailAddress ea ON ea.EmailId = stats.EmailId

 

  ----WHERE ea.EmailAddress LIKE '%domain1.com%'

 

  ----    OR ea.EmailAddress LIKE '%domain2.com%'

 

)

 

SELECT COUNT(1) AS archivesCount, SUM(statsPerArchive_CTE.Count) AS TotalCount, SUM(statsPerArchive_CTE.[Total Size MB]) AS TotalSizeMb

 

FROM statsPerArchive_CTE

 

 

 

DROP TABLE ExchangeArchiveStats

 

 

 

SET @log = 'Finished getting overal stats';

 

RAISERROR(@log, 0, 0) WITH NOWAIT;

Overall statistics per single archive

-- drop tables if still exists

 

IF OBJECT_ID('dbo.ArchiveEmailIds') IS NOT NULL DROP TABLE ArchiveEmailIds

 

IF OBJECT_ID('dbo.ExchangeArchiveStats') IS NOT NULL DROP TABLE ExchangeArchiveStats

 

-- Get all Exchange archive stats in batches

 

-- @count variable defines number of archive for which we get stats in one batch.

 

DECLARE @ErrorMessage NVARCHAR(4000);

 

DECLARE @log AS NVARCHAR(4000);

 

DECLARE @journal BIGINT = -3515994502517958093;

 

DECLARE @pstFileType INT = 4;

 

DECLARE @smtpFileType INT = 3;

 

DECLARE @exchangeArchiveType INT = 1;

 

DECLARE @count INT = 10000;

 

DECLARE @start INT = 0;

 

DECLARE @total INT = 0;

 

DECLARE @affectedRowsCount INT = 1;

 

 

 

-- create tables used to store stats

 

CREATE TABLE ArchiveEmailIds (

 

  EmailId BIGINT NOT NULL

 

)

 

 

 

CREATE TABLE ExchangeArchiveStats (

 

  [EmailId] BIGINT NOT NULL,

 

  [Count] BIGINT NULL,

 

  [Total Size MB] BIGINT NULL

 

)

 

 

 

-- get exchange archive stats

 

SET @log = 'Started getting stats for Exchange archives.';

 

RAISERROR(@log, 0, 0) WITH NOWAIT;

 

 

 

BEGIN TRY

 

  WHILE (@affectedRowsCount > 0)

 

  BEGIN

 

      TRUNCATE TABLE ArchiveEmailIds

 

 

 

      ;WITH CTE_RouteMasks AS

 

      (

 

          SELECT r.EmailId, COUNT(1) AS RouteCount FROM dbo.Route r

 

          WHERE ([r].[RouteMask] & 4 = 4)

 

          GROUP BY r.EmailId

 

      )

 

      INSERT INTO ArchiveEmailIds

 

      SELECT TOP(@count) [x].[EmailId]

 

      FROM (

 

          SELECT ROW_NUMBER() OVER (ORDER BY [ea].[EmailId]) AS [ROW_NUMBER], [ea].[EmailId], [ea].[EmailAddress], [ea].[Type]

 

          FROM [EmailAddress] AS [ea]

 

          WHERE (ea.Type = @exchangeArchiveType OR ea.Type IS NULL) AND EXISTS(

 

              SELECT NULL AS [EMPTY]

 

              FROM CTE_RouteMasks AS [r]

 

              WHERE [r].[EmailId] = [ea].[EmailId]

 

              )

 

          ) AS [x]

 

      WHERE [x].[ROW_NUMBER] > @start

 

      ORDER BY [x].[ROW_NUMBER]

 

 

 

      INSERT INTO ExchangeArchiveStats

 

      SELECT r.EmailId, COUNT_BIG(DISTINCT(r.[MessageID])) as [Count], ROUND(SUM(CAST(m.MsgSize AS REAL)) / 1024 / 1024, 2) AS [Total Size MB]

 

      FROM Route r

 

          INNER JOIN ArchiveEmailIds tmp ON tmp.EmailId = r.EmailId

 

          INNER JOIN dbo.FolderMessage fm ON r.MessageID = fm.MessageID AND r.FolderId = fm.FolderId and r.PartitionId = fm.PartitionId

 

          INNER JOIN dbo.Message m ON m.MessageID = r.MessageID AND m.PartitionId = r.PartitionId

 

          INNER JOIN dbo.Volume v ON v.VolumeId = fm.VolumeId

 

      WHERE r.RouteMask & 4 = 4

 

      group by r.[EmailId];

 

 

 

      SET @affectedRowsCount = @@ROWCOUNT;

 

      SET @start = @start + @count;

 

      SET @total = @total + @affectedRowsCount;

 

      SET @log = 'Got stats for ' + CAST(@affectedRowsCount AS CHAR(10)) + ' archives';

 

      RAISERROR(@log, 0, 0) WITH NOWAIT;

 

  END

 

 

 

  --drop temp tables

 

  DROP TABLE ArchiveEmailIds

 

END TRY

 

BEGIN CATCH

 

  DROP TABLE ArchiveEmailIds

 

  DROP TABLE ExchangeArchiveStats

 

  SELECT

 

  @ErrorMessage=ERROR_MESSAGE();

 

  RAISERROR('Error while getting exchange archives. Error: %s', 15, 1, @ErrorMessage)

 

END CATCH

 

 

 

SET @log = 'Finished getting stats for Exchange archives. Got total ' + CAST(@total AS CHAR(10)) + ' archives';

 

RAISERROR(@log, 0, 0) WITH NOWAIT;

 

SET @log = 'Started getting overal stats';

 

RAISERROR(@log, 0, 0) WITH NOWAIT;

 

-- get pst and journal stats

 

-- show all the stats results

 

;WITH journalAndPstStats_CTE AS (

 

  SELECT r.EmailId, COUNT_BIG(DISTINCT(r.[MessageID])) as [Count], ROUND(SUM(CAST(m.MsgSize AS REAL)) / 1024 / 1024, 2) AS [Total Size MB]

 

  FROM dbo.EmailAddress ea

 

      INNER JOIN dbo.Route r ON r.EmailId = ea.EmailId AND (ea.EmailId = @journal OR ea.Type = @pstFileType)

 

      INNER JOIN dbo.FolderMessage fm ON r.MessageID = fm.MessageID AND r.FolderId = fm.FolderId and r.PartitionId = fm.PartitionId

 

      INNER JOIN dbo.Message m ON m.MessageID = r.MessageID AND m.PartitionId = r.PartitionId

 

      INNER JOIN dbo.Volume v ON v.VolumeId = fm.VolumeId

 

  group by r.[EmailId]

 

)

-- !!! Enable this query only in case SMTP stats should be shown !!!

-- , statsSmtpArchive_CTE AS (

--

--    SELECT r.EmailId, COUNT_BIG(DISTINCT(r.[MessageID])) as [Count], ROUND(SUM(CAST(m.MsgSize AS REAL)) / 1024 / 1024, 2) AS [Total Size MB]

--

--    FROM dbo.EmailAddress ea

--

--        INNER JOIN dbo.Route r ON r.EmailId = ea.EmailId AND ea.Type = @smtpFileType

--

--        INNER JOIN dbo.FolderMessage fm ON r.MessageID = fm.MessageID AND r.FolderId = fm.FolderId and r.PartitionId = fm.PartitionId

--

--        INNER JOIN dbo.Message m ON m.MessageID = r.MessageID AND m.PartitionId = r.PartitionId

--

--        INNER JOIN dbo.Volume v ON v.VolumeId = fm.VolumeId    

--

--    group by r.[EmailId]

--

-- )

 

, statsPerArchive_CTE AS (

 

  SELECT ea.EmailAddress, ea.EmailId, ea.Type, stats.Count, stats.[Total Size MB] FROM ExchangeArchiveStats stats

 

      INNER JOIN dbo.EmailAddress ea ON ea.EmailId = stats.EmailId        

 

  UNION

 

  SELECT ea.EmailAddress, ea.EmailId, ea.Type, stats.Count, stats.[Total Size MB] FROM journalAndPstStats_CTE stats

 

      INNER JOIN dbo.EmailAddress ea ON ea.EmailId = stats.EmailId

 

  -- !!! Enable this UNION only in case SMTP stats should be shown !!!

 

  --UNION

 

  --SELECT ea.EmailAddress, ea.EmailId, ea.Type, stats.Count, stats.[Total Size MB]

 

  --FROM statsSmtpArchive_CTE stats

 

  --    INNER JOIN dbo.EmailAddress ea ON ea.EmailId = stats.EmailId

 

  ------ !!! use statement to define domain list you are interested in !!!

 

  ----WHERE ea.EmailAddress LIKE '%domain1.com%'

 

  ----    OR ea.EmailAddress LIKE '%domain2.com%'        

 

)

 

SELECT * FROM statsPerArchive_CTE

 

--WHERE statsPerArchive_CTE.Type = 1

 

ORDER BY statsPerArchive_CTE.Type

 

-- Type (EmailAddress.Type)

 

-- 1 - Eachange archive

 

-- 3 - SMTP archive

 

-- 4 - PST archive

 

-- 7 - Journal archive

 

 

 

DROP TABLE ExchangeArchiveStats

 

 

 

SET @log = 'Finished getting overal stats';

 

RAISERROR(@log, 0, 0) WITH NOWAIT;

Overall statistics per single archive with date filtering

-- drop tables if still exists

 

IF OBJECT_ID('dbo.ArchiveEmailIds') IS NOT NULL DROP TABLE ArchiveEmailIds

 

IF OBJECT_ID('dbo.ExchangeArchiveStats') IS NOT NULL DROP TABLE ExchangeArchiveStats

 

-- Get all Exchange archive stats in batches

 

-- @count variable defines number of archive for which we get stats in one batch.

 

DECLARE @ErrorMessage NVARCHAR(4000);

 

DECLARE @log AS NVARCHAR(4000);

 

DECLARE @journal BIGINT = -3515994502517958093;

 

DECLARE @pstFileType INT = 4;

 

DECLARE @smtpFileType INT = 3;

 

DECLARE @exchangeArchiveType INT = 1;

 

DECLARE @count INT = 10000;

 

DECLARE @start INT = 0;

 

DECLARE @total INT = 0;

 

DECLARE @affectedRowsCount INT = 1;

 

DECLARE @msgDateFrom DATETIME = '01.01.2010';

 

DECLARE @msgDateTo DATETIME = '12.31.2020';

 

 

 

-- create tables used to store stats

 

CREATE TABLE ArchiveEmailIds (

 

  EmailId BIGINT NOT NULL

 

)

 

 

 

CREATE TABLE ExchangeArchiveStats (

 

  [EmailId] BIGINT NOT NULL,

 

  [Count] BIGINT NULL,

 

  [Total Size MB] BIGINT NULL

 

)

 

 

 

-- get exchange archive stats

 

SET @log = 'Started getting stats for Exchange archives.';

 

RAISERROR(@log, 0, 0) WITH NOWAIT;

 

 

 

BEGIN TRY

 

  WHILE (@affectedRowsCount > 0)

 

  BEGIN

 

      TRUNCATE TABLE ArchiveEmailIds

 

 

 

      ;WITH CTE_RouteMasks AS

 

      (

 

          SELECT r.EmailId, COUNT(1) AS RouteCount FROM dbo.Route r

 

          WHERE ([r].[RouteMask] & 4 = 4)

 

          GROUP BY r.EmailId

 

      )

 

      INSERT INTO ArchiveEmailIds

 

      SELECT TOP(@count) [x].[EmailId]

 

      FROM (

 

          SELECT ROW_NUMBER() OVER (ORDER BY [ea].[EmailId]) AS [ROW_NUMBER], [ea].[EmailId], [ea].[EmailAddress], [ea].[Type]

 

          FROM [EmailAddress] AS [ea]

 

          WHERE (ea.Type = @exchangeArchiveType OR ea.Type IS NULL) AND EXISTS(

 

              SELECT NULL AS [EMPTY]

 

              FROM CTE_RouteMasks AS [r]

 

              WHERE [r].[EmailId] = [ea].[EmailId]

 

              )

 

          ) AS [x]

 

      WHERE [x].[ROW_NUMBER] > @start

 

      ORDER BY [x].[ROW_NUMBER]

 

 

 

      INSERT INTO ExchangeArchiveStats

 

      SELECT r.EmailId, COUNT_BIG(DISTINCT(r.[MessageID])) as [Count], ROUND(SUM(CAST(m.MsgSize AS REAL)) / 1024 / 1024, 2) AS [Total Size MB]

 

      FROM Route r

 

          INNER JOIN ArchiveEmailIds tmp ON tmp.EmailId = r.EmailId

 

          INNER JOIN dbo.FolderMessage fm ON r.MessageID = fm.MessageID AND r.FolderId = fm.FolderId and r.PartitionId = fm.PartitionId

 

          INNER JOIN dbo.Message m ON m.MessageID = r.MessageID AND m.PartitionId = r.PartitionId

 

          INNER JOIN dbo.Volume v ON v.VolumeId = fm.VolumeId

 

      WHERE r.RouteMask & 4 = 4

 

          -- !!! if you don't want to use date filter remove condition below !!!

 

          AND m.MsgDate >= @msgDateFrom AND m.MsgDate <= @msgDateTo

 

      group by r.[EmailId];

 

 

 

      SET @affectedRowsCount = @@ROWCOUNT;

 

      SET @start = @start + @count;

 

      SET @total = @total + @affectedRowsCount;

 

      SET @log = 'Got stats for ' + CAST(@affectedRowsCount AS CHAR(10)) + ' archives';

 

      RAISERROR(@log, 0, 0) WITH NOWAIT;

 

  END

 

 

 

  --drop temp tables

 

  DROP TABLE ArchiveEmailIds

 

END TRY

 

BEGIN CATCH

 

  DROP TABLE ArchiveEmailIds

 

  DROP TABLE ExchangeArchiveStats

 

  SELECT

 

  @ErrorMessage=ERROR_MESSAGE();

 

  RAISERROR('Error while getting exchange archives. Error: %s', 15, 1, @ErrorMessage)

 

END CATCH

 

 

 

SET @log = 'Finished getting stats for Exchange archives. Got total ' + CAST(@total AS CHAR(10)) + ' archives';

 

RAISERROR(@log, 0, 0) WITH NOWAIT;

 

SET @log = 'Started getting overal stats';

 

RAISERROR(@log, 0, 0) WITH NOWAIT;

 

 

 

-- get pst and journal stats

 

-- show all the stats results

 

;WITH journalAndPstStats_CTE AS (

 

  SELECT r.EmailId, COUNT_BIG(DISTINCT(r.[MessageID])) as [Count], ROUND(SUM(CAST(m.MsgSize AS REAL)) / 1024 / 1024, 2) AS [Total Size MB]

 

  FROM dbo.EmailAddress ea

 

      INNER JOIN dbo.Route r ON r.EmailId = ea.EmailId AND (ea.EmailId = @journal OR ea.Type = @pstFileType)

 

      INNER JOIN dbo.FolderMessage fm ON r.MessageID = fm.MessageID AND r.FolderId = fm.FolderId and r.PartitionId = fm.PartitionId

 

      INNER JOIN dbo.Message m ON m.MessageID = r.MessageID AND m.PartitionId = r.PartitionId

 

      INNER JOIN dbo.Volume v ON v.VolumeId = fm.VolumeId

 

  -- !!! if you don't want to use dat filter remove condition below !!!

 

  WHERE m.MsgDate >= @msgDateFrom AND m.MsgDate <= @msgDateTo

 

  group by r.[EmailId]

 

)

-- !!! Enable this query only in case SMTP stats should be shown !!!

-- , statsSmtpArchive_CTE AS (

--

--    SELECT r.EmailId, COUNT_BIG(DISTINCT(r.[MessageID])) as [Count], ROUND(SUM(CAST(m.MsgSize AS REAL)) / 1024 / 1024, 2) AS [Total Size MB]

--

--    FROM dbo.EmailAddress ea

--

--        INNER JOIN dbo.Route r ON r.EmailId = ea.EmailId AND ea.Type = @smtpFileType

--

--        INNER JOIN dbo.FolderMessage fm ON r.MessageID = fm.MessageID AND r.FolderId = fm.FolderId and r.PartitionId = fm.PartitionId

--

--        INNER JOIN dbo.Message m ON m.MessageID = r.MessageID AND m.PartitionId = r.PartitionId

--

--        INNER JOIN dbo.Volume v ON v.VolumeId = fm.VolumeId

--

--    -- !!! if you don't want to use dat filter remove condition below !!!

--

--    WHERE m.MsgDate >= @msgDateFrom AND m.MsgDate <= @msgDateTo

--

--    group by r.[EmailId]

--

-- )

 

, statsPerArchive_CTE AS (

 

  SELECT ea.EmailAddress, ea.EmailId, ea.Type, stats.Count, stats.[Total Size MB] FROM ExchangeArchiveStats stats

 

      INNER JOIN dbo.EmailAddress ea ON ea.EmailId = stats.EmailId        

 

  UNION

 

  SELECT ea.EmailAddress, ea.EmailId, ea.Type, stats.Count, stats.[Total Size MB] FROM journalAndPstStats_CTE stats

 

      INNER JOIN dbo.EmailAddress ea ON ea.EmailId = stats.EmailId

 

  -- !!! Enable this UNION only in case SMTP stats should be shown !!!

 

  --UNION

 

  --SELECT ea.EmailAddress, ea.EmailId, ea.Type, stats.Count, stats.[Total Size MB]

 

  --FROM statsSmtpArchive_CTE stats

 

  --    INNER JOIN dbo.EmailAddress ea ON ea.EmailId = stats.EmailId

 

  ---- !!! use statement to define domain list you are interested in !!!

 

  ----WHERE ea.EmailAddress LIKE '%domain1.com%'

 

  ----    OR ea.EmailAddress LIKE '%domain2.com%'

 

)

 

SELECT * FROM statsPerArchive_CTE

 

--WHERE statsPerArchive_CTE.Type = 1

 

ORDER BY statsPerArchive_CTE.Type

 

-- Type (EmailAddress.Type)

 

-- 1 - Eachange archive

 

-- 3 - SMTP archive

 

-- 4 - PST archive

 

-- 7 - Journal archive

 

 

 

DROP TABLE ExchangeArchiveStats

 

 

 

SET @log = 'Finished getting overal stats';

 

RAISERROR(@log, 0, 0) WITH NOWAIT;