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.

Print Friendly, PDF & Email