Issue
In some environments when the EV Collector Modules attempts to collect metadata from large journal archives, a SQL timeout may be experienced. This article explains possible ways to work around this.
Solution
It is strongly recommended to review the SQL Server specification versus the recommended levels in the Archive Shuttle documentation.
The following error below may be seen in the EV Collector Module log file. The error is about timeout from EV SQL, as EV Collector is collecting journal item metadata from EV SQL server.

2015-02-18 21:36:43Z|6216| 13|ERROR|
CollectItemsForSingleArchive|Error while collecting items for Journal
Archive 01. Void OnError(System.Data.SqlClient.SqlException, Boolean) Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.  at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)  at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)  at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) 
 at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
 bulkCopyHandler, TdsParserStateObject stateObj)  at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()  at System.Data.SqlClient.SqlDataReader.get_MetaData()  at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) 
 at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior
 cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean
async)  at
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String
method, DbAsyncResult result)  at
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String
method)  at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)  at System.Data.SqlClient.SqlCommand.ExecuteReader() 
 at
ArchiveShuttle.Module.EVCollectorModule.EvItemsGatherer.QueryArchivedItems(EVArchive
 evArchive, DateTime colFrom, String sqlstring,
SqlConnectionStringBuilder connectionString, Int64 pageNumber, Int64
rowsPerPage)  at ArchiveShuttle.Module.EVCollectorModule.EvItemsGatherer.GatherArchivedItems(EVArchive archive, DateTime colFrom)  at ArchiveShuttle.Module.EVCollectorModule.EvItemsGatherer.CollectItemsForSingleArchive(EVArchive archive)

The EV Collect module .exe.config file has two settings which can be adjusted in order to try to work around this issue.

<appSettings>
<add key="WebserviceUrl" value="http://l1-rowias-11/ASWS/ArchiveShuttle.svc"/>
<add key="ClientSettingsProvider.ServiceUri" value=""/>
<add key="LocalLogLevelOverride" value=""/>
<add key="WebServiceLogLevelOverride" value=""/>
<add key="LocalLogFilePath" value="C:\Program Files (x86)\QUADROtech\Logs\AS_EVCollector_Log.txt"/>
<add key="LocalDBName" value="EVCollectorDB.sdf"/>
<!-- SQL command timeout used when collecting items for archive.
The time in seconds to wait for the command to execute. The default is 600 seconds.
It has to be integer value and greater than 0.
-->
<!--<add key="SqlCommandTimeout" value="600"/>-->
</appSettings>

Change the SQL Command Timeout from 600 to 1200 or 1800. The above entries in the config file would now look like this:

<appSettings>
<add key="WebserviceUrl" value="http://l1-rowias-11/ASWS/ArchiveShuttle.svc"/>
<add key="ClientSettingsProvider.ServiceUri" value=""/>
<add key="LocalLogLevelOverride" value=""/>
<add key="WebServiceLogLevelOverride" value=""/>
<add key="LocalLogFilePath" value="C:\Program Files (x86)\QUADROtech\Logs\AS_EVCollector_Log.txt"/>
<add key="LocalDBName" value="EVCollectorDB.sdf"/>
<!-- SQL command timeout used when collecting items for archive.
The time in seconds to wait for the command to execute. The default is 600 seconds.
It has to be integer value and greater than 0.
-->
 <add key="SqlCommandTimeout" value="1200"/>
</appSettings>

Change the RowsPerPage option to return large batches of data during each cycle. The default value is shown below (near the bottom of the .exe.config file)

<setting name="RowsPerPage" serializeAs="String">
<value>100000</value>
</setting>

Change the value to 250000:

<setting name="RowsPerPage" serializeAs="String">
<value>250000</value>
</setting>

Once these changes have been made, restart the EV Collector Service.
 
 

Print Friendly, PDF & Email