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.