Issue

You may need to find statistics about your source environment. Below are queries you can use to find statistics such as:

  • Overall stats
  • Overall stats with date filtering
  • Stats per single archive
  • Stats per single archive with date filtering

Show overall stats

-- 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

DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @log AS NVARCHAR(4000);

DECLARE @count INT;
SET @count = 10000;
DECLARE @start INT;
SET @start = 0;
DECLARE @total INT;
SET @total = 0;
DECLARE @affectedRowsCount INT;
SET @affectedRowsCount = 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 archive stats.';
RAISERROR(@log, 0, 0) WITH NOWAIT;

BEGIN TRY
WHILE (@affectedRowsCount > 0)
BEGIN
TRUNCATE TABLE ArchiveEmailIds

INSERT INTO ArchiveEmailIds
select Top(@count) EmailID from
( select EMailId, ROW_NUMBER() over (order by EMailID) as RowId, EmailAddress from EmailAddress ea
where EmailAddress LIKE 'EX%'
) x where RowID > @start
ORDER by RowID

INSERT INTO ExchangeArchiveStats
SELECT [r].EmailId, count(DISTINCT(r.[MD5HashKey])) as [Count], ROUND(SUM(CAST(m.MsgSize AS REAL)) / 1024 / 1024, 2) AS [Total Size MB]
FROM [Route] AS [r]
INNER JOIN [ArchiveEmailIds] as [ids] ON [r].EmailId = [ids].EmailId
INNER JOIN [Message] as [m] ON [r].[MD5HashKey] = [m].[MD5HashKey] and [r].[TimeStamp] = [m].[TimeStamp]
INNER JOIN [VolumeMessage] AS [vm] ON [m].[MD5HashKey] = [vm].[MD5HashKey] and [m].[TimeStamp] = [vm].[TimeStamp]
INNER JOIN [Volume] AS [v] ON [vm].[VolumeId] = [v].[VolumeId]
INNER JOIN [Folder] AS [f] ON [r].[FolderId] = [f].[FolderId]
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 overall stats';
RAISERROR(@log, 0, 0) WITH NOWAIT;

-- get pst and journal stats
-- show all the stats results
SELECT Count(1) as archivesCount, SUM(stats.Count) as TotalCount, SUM(stats.[Total Size MB]) as TotalSizeMb FROM ExchangeArchiveStats stats
INNER JOIN dbo.EmailAddress ea ON ea.EmailId = stats.EmailId

DROP TABLE ExchangeArchiveStats

SET @log = 'Finished getting overal stats';
RAISERROR(@log, 0, 0) WITH NOWAIT;

Show overall stats with date filter

-- 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

DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @log AS NVARCHAR(4000);

DECLARE @count INT;
SET @count = 10000;
DECLARE @start INT;
SET @start = 0;
DECLARE @total INT;
SET @total = 0;
DECLARE @affectedRowsCount INT;
SET @affectedRowsCount = 1;

DECLARE @msgDateFrom DATETIME;
SET @msgDateFrom = '01.01.2010';
DECLARE @msgDateTo DATETIME;
SET @msgDateTo = '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 archive stats.';
RAISERROR(@log, 0, 0) WITH NOWAIT;

BEGIN TRY
WHILE (@affectedRowsCount > 0)
BEGIN
TRUNCATE TABLE ArchiveEmailIds

INSERT INTO ArchiveEmailIds
select Top(@count) EmailID from
( select EMailId, ROW_NUMBER() over (order by EMailID) as RowId, EmailAddress from EmailAddress ea
where EmailAddress LIKE 'EX%'
) x where RowID > @start
ORDER by RowID

INSERT INTO ExchangeArchiveStats
SELECT [r].EmailId, count(DISTINCT(r.[MD5HashKey])) as [Count], ROUND(SUM(CAST(m.MsgSize AS REAL)) / 1024 / 1024, 2) AS [Total Size MB]
FROM [Route] AS [r]
INNER JOIN [ArchiveEmailIds] as [ids] ON [r].EmailId = [ids].EmailId
INNER JOIN [Message] as [m] ON [r].[MD5HashKey] = [m].[MD5HashKey] and [r].[TimeStamp] = [m].[TimeStamp]
INNER JOIN [VolumeMessage] AS [vm] ON [m].[MD5HashKey] = [vm].[MD5HashKey] and [m].[TimeStamp] = [vm].[TimeStamp]
INNER JOIN [Volume] AS [v] ON [vm].[VolumeId] = [v].[VolumeId]
INNER JOIN [Folder] AS [f] ON [r].[FolderId] = [f].[FolderId]
WHERE 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 overall stats';
RAISERROR(@log, 0, 0) WITH NOWAIT;

-- get pst and journal stats
-- show all the stats results
SELECT Count(1) as archivesCount, SUM(stats.Count) as TotalCount, SUM(stats.[Total Size MB]) as TotalSizeMb FROM ExchangeArchiveStats stats
INNER JOIN dbo.EmailAddress ea ON ea.EmailId = stats.EmailId

DROP TABLE ExchangeArchiveStats

SET @log = 'Finished getting overal stats';
RAISERROR(@log, 0, 0) WITH NOWAIT;

Show overall stats 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

DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @log AS NVARCHAR(4000);

DECLARE @count INT;
SET @count = 10000;
DECLARE @start INT;
SET @start = 0;
DECLARE @total INT;
SET @total = 0;
DECLARE @affectedRowsCount INT;
SET @affectedRowsCount = 1;

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 archive stats.';
RAISERROR(@log, 0, 0) WITH NOWAIT;

BEGIN TRY
WHILE (@affectedRowsCount > 0)
BEGIN
TRUNCATE TABLE ArchiveEmailIds

INSERT INTO ArchiveEmailIds
select Top(@count) EmailID from
( select EMailId, ROW_NUMBER() over (order by EMailID) as RowId, EmailAddress from EmailAddress ea
where EmailAddress LIKE 'EX%'
) x where RowID > @start
ORDER by RowID

INSERT INTO ExchangeArchiveStats
SELECT [r].EmailId, count(DISTINCT(r.[MD5HashKey])) as [Count], ROUND(SUM(CAST(m.MsgSize AS REAL)) / 1024 / 1024, 2) AS [Total Size MB]
FROM [Route] AS [r]
INNER JOIN [ArchiveEmailIds] as [ids] ON [r].EmailId = [ids].EmailId
INNER JOIN [Message] as [m] ON [r].[MD5HashKey] = [m].[MD5HashKey] and [r].[TimeStamp] = [m].[TimeStamp]
INNER JOIN [VolumeMessage] AS [vm] ON [m].[MD5HashKey] = [vm].[MD5HashKey] and [m].[TimeStamp] = [vm].[TimeStamp]
INNER JOIN [Volume] AS [v] ON [vm].[VolumeId] = [v].[VolumeId]
INNER JOIN [Folder] AS [f] ON [r].[FolderId] = [f].[FolderId]
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 overall stats';
RAISERROR(@log, 0, 0) WITH NOWAIT;

-- get pst and journal stats
-- show all the stats results
SELECT ea.EmailAddress, ea.EmailId, stats.Count, stats.[Total Size MB] FROM ExchangeArchiveStats stats
INNER JOIN dbo.EmailAddress ea ON ea.EmailId = stats.EmailId

DROP TABLE ExchangeArchiveStats

SET @log = 'Finished getting overal stats';
RAISERROR(@log, 0, 0) WITH NOWAIT;

Show overall stats 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

DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @log AS NVARCHAR(4000);

DECLARE @count INT;
SET @count = 10000;
DECLARE @start INT;
SET @start = 0;
DECLARE @total INT;
SET @total = 0;
DECLARE @affectedRowsCount INT;
SET @affectedRowsCount = 1;

DECLARE @msgDateFrom DATETIME;
SET @msgDateFrom = '01.01.2010';
DECLARE @msgDateTo DATETIME;
SET @msgDateTo = '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 archive stats.';
RAISERROR(@log, 0, 0) WITH NOWAIT;

BEGIN TRY
WHILE (@affectedRowsCount > 0)
BEGIN
TRUNCATE TABLE ArchiveEmailIds

INSERT INTO ArchiveEmailIds
select Top(@count) EmailID from
( select EMailId, ROW_NUMBER() over (order by EMailID) as RowId, EmailAddress from EmailAddress ea
where EmailAddress LIKE 'EX%'
) x where RowID > @start
ORDER by RowID

INSERT INTO ExchangeArchiveStats
SELECT [r].EmailId, count(DISTINCT(r.[MD5HashKey])) as [Count], ROUND(SUM(CAST(m.MsgSize AS REAL)) / 1024 / 1024, 2) AS [Total Size MB]
FROM [Route] AS [r]
INNER JOIN [ArchiveEmailIds] as [ids] ON [r].EmailId = [ids].EmailId
INNER JOIN [Message] as [m] ON [r].[MD5HashKey] = [m].[MD5HashKey] and [r].[TimeStamp] = [m].[TimeStamp]
INNER JOIN [VolumeMessage] AS [vm] ON [m].[MD5HashKey] = [vm].[MD5HashKey] and [m].[TimeStamp] = [vm].[TimeStamp]
INNER JOIN [Volume] AS [v] ON [vm].[VolumeId] = [v].[VolumeId]
INNER JOIN [Folder] AS [f] ON [r].[FolderId] = [f].[FolderId]
WHERE 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 overall stats';
RAISERROR(@log, 0, 0) WITH NOWAIT;

-- get pst and journal stats
-- show all the stats results
SELECT ea.EmailAddress, ea.EmailId, stats.Count, stats.[Total Size MB] FROM ExchangeArchiveStats stats
INNER JOIN dbo.EmailAddress ea ON ea.EmailId = stats.EmailId

DROP TABLE ExchangeArchiveStats

SET @log = 'Finished getting overal stats';
RAISERROR(@log, 0, 0) WITH NOWAIT;

Print Friendly, PDF & Email