Issue
In some environments when the EAS Module attempts to collect archives, a SQL timeout may be experienced. This article explains possible ways to work around this.
Solution
The following error below may be seen in the EAS Module log file. The error is about timeout from EAS SQL, as EAS Module is collecting archives from the EAS SQL server.
2017-01-06 19:57:10Z|3344| 13|ERROR| Process|Error during gathering EAS environment System.Data.DataTable Query(System.String, System.Collections.Generic.IDictionary`2[System.String,System.Object])
Error SELECT u.USERID as UserId, u.USERNAME AS UserName, u.OBJDISTNAME as ObjDistName, g.GROUPNAME as GroupName, COUNT(deduped.MSGID) as ItemsCount, SUM(p.MSGSIZE) AS ItemsSize FROM (SELECT r.MSGID, MAX(r.FOLDERID) as FOLDERID FROM dbo.REFER r WHERE r.MSGID >= 0 AND r.FOLDERID >=0 GROUP BY r.USERID, r.MSGID) deduped INNER JOIN dbo.REFER rf ON deduped.MSGID = rf.MSGID AND deduped.FOLDERID = rf.FOLDERID INNER JOIN dbo.FOLDER f ON deduped.FOLDERID = f.FOLDERID INNER JOIN dbo.PROFILE p ON deduped.MSGID = p.MSGID INNER JOIN dbo.USERS u ON rf.USERID = u.USERID LEFT JOIN dbo.GROUPS g ON g.GROUPID = u.GROUPID GROUP BY u.USERID, u.USERNAME, u.OBJDISTNAME, g.GROUPNAME
at ArchiveShuttle.Module.EasModule.DatabaseHelpers.Query(String sql, IDictionary`2 parameters)
at ArchiveShuttle.Module.EasModule.MetadataCollector.CollectArchives()
at ArchiveShuttle.Module.EasModule.CommandProcessor.CollectArchivesProcessor.Process(ModuleCommand command, IModuleClient moduleClient)
Void OnError(System.Data.SqlClient.SqlException, Boolean, System.Action`1[System.Action])
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
The EAS module.exe.config file (ArchiveShuttle.Module.EasModule.exe.config) has two settings which can be adjusted in order to try to work around this issue.
<applicationSettings>
<ArchiveShuttle.Module.EasModule.Properties.Settings>
<setting name="LogPerformance" serializeAs="String">
<value>True</value>
</setting>
<setting name="EASSQLTimeout" serializeAs="String">
<value>600</value>
</setting>
<!-- Batch size for archive collecting, if set to 0, batch size is not used-->
<setting name="CollectArchiveBatchSize" serializeAs="String">
<value>0</value>
</setting>
</ArchiveShuttle.Module.EasModule.Properties.Settings>
</applicationSettings>
Increase the SQL Command Timeout from default value 600, to 1200, 1800 or more if needed. The above entries in the config file would now look like this:
<applicationSettings>
<ArchiveShuttle.Module.EasModule.Properties.Settings>
<setting name="LogPerformance" serializeAs="String">
<value>True</value>
</setting>
<setting name="EASSQLTimeout" serializeAs="String">
<value>1800</value>
</setting>
<!-- Batch size for archive collecting, if set to 0, batch size is not used-->
<setting name="CollectArchiveBatchSize" serializeAs="String">
<value>0</value>
</setting>
</ArchiveShuttle.Module.EasModule.Properties.Settings>
</applicationSettings>
Note: There is also setting for batching available. In some cases, it might be needed to do collection in batches.
<setting name="CollectArchiveBatchSize" serializeAs="String">
<value>0</value>
Batch size for archive collecting, if set to 0, the batch size is not used.
Batch size for archive collecting, if set to e.g. 100000, the collection will be executed in batches of 100000 items.
Once these changes have been made, restart the EAS Module Service.