Overview

There might be particular per-item message size limits in a target environment. A filter can be used to ensure that items above this limit are not migrated.  This article provides some SQL queries that can be used in order to identify, which archives have items over certain size limit.

Solution

Item Gathering must be run on the source archives in order to get the metadata required for performing such search. Assumption is, that the Item table in the Item Database has been populated.
Following query lists all EV archives together with number of items which are larger than 25 Mb in the column “Num of Oversized”.

SELECT eva.Name AS 'EVArchive Name', COUNT(i.ItemId) AS 'Num of Items', SUM(CASE WHEN i.OriginalSize > 1024 * 1024 * 25 THEN 1 ELSE 0 END) AS 'Num of Oversized', MAX(i.OriginalSize) AS 'Largest Size'
FROM dbo.EVArchive eva, [your-item-database-name].dbo.Item i
WHERE i.ContainerId = eva.ContainerId
GROUP BY eva.Name
ORDER BY 'Num of Oversized' DESC;

and following query lists only such EV archives, that contain items with size > 25 Mb:

SELECT dbo.EVArchive.Name, COUNT(*) AS 'Num of Oversized'
FROM [your-item-database-name].dbo.Item i
INNER JOIN dbo.ContainerToUser ON dbo.ContainerToUser.ContainerId = i.ContainerId
INNER JOIN dbo.EVArchive ON dbo.ContainerToUser.UserSid = dbo.EVArchive.SID
WHERE OriginalSize > 26214400
GROUP BY dbo.EVArchive.Name;

Note

Run both queries against Archive Shuttle Directory Database and replace [your-item-database-name] with the name of particular Item Database.

Print Friendly, PDF & Email