TFS - Test Management Warehouse Sync - SQL Timeout

Question

Ever since we upgraded TFS 2013 - TFS 2013 update 2 our "Test Management Warehouse" fails to sync. It throws a SQL timeout after 1 hour.

We run a heck of alot of automation tests, thus our test management warehouse is reasonably large.

Here is a thread to a microsoft issue regarding the same kind of thing but for the "Build Warehouse Sync" - My guess is its kind of the same issue however we dont have many builds but have tonnes of tests.

Things I've done to try and fix:

Run a full Rebuild (Drops warehouse and starts again)
- No effect
Rebuilt all the indexes on the tfs_defaultcollection
- Queries run faster now but no effect on issue
Increased the timeout "WarehouseCommandSqlTimeout" from 1 hour to 3 hours
- Still fails after 1 hour so my guess is there is a global SQL connection timeout of 1 hour somewhere??

As a hotfix for now, id like to increase the SQL connection timeout to 3 hours and see if that makes a difference. Does anyone know where that setting is?

This is the error:

[Test Management Warehouse Sync]: ---> Microsoft.TeamFoundation.Warehouse.WarehouseException: TF221122: An error occurred running job Test Management Warehouse Sync for team project collection or Team Foundation server DefaultCollection. ---> Microsoft.TeamFoundation.Framework.Server.DatabaseOperationTimeoutException: TF246018: The database operation exceeded the timeout limit and has been cancelled. Verify that the parameters of the operation are correct. ---> System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception: The wait operation timed out --- End of inner exception stack trace --- at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() 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, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean asyncWrite) 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(CommandBehavior behavior) at Microsoft.TeamFoundation.Framework.Server.TeamFoundationSqlResourceComponent.Execute(ExecuteType executeType, CommandBehavior behavior) --- End of inner exception stack trace --- at Microsoft.TeamFoundation.Framework.Server.TeamFoundationSqlResourceComponent.TranslateException(Int32 errorNumber, SqlException sqlException, SqlError sqlError) at Microsoft.TeamFoundation.Framework.Server.TeamFoundationSqlResourceComponent.TranslateException(SqlException sqlException) at Microsoft.TeamFoundation.Framework.Server.TeamFoundationSqlResourceComponent.MapException(SqlException ex, QueryExecutionState queryState) at Microsoft.TeamFoundation.Framework.Server.TeamFoundationSqlResourceComponent.HandleException(Exception exception) at Microsoft.TeamFoundation.Framework.Server.TeamFoundationSqlResourceComponent.Execute(ExecuteType executeType, CommandBehavior behavior) at Microsoft.TeamFoundation.Framework.Server.TeamFoundationSqlResourceComponent.ExecuteReader() at Microsoft.TeamFoundation.TestManagement.Warehouse.WarehouseResultDatabase.QueryTestPointData(SqlBinary watermark, Int32 limit, ProcessRowCallback deletedCallback, ProcessMappingDataCallback addedCallback, ResolveIdentities resolveIdentitiesCallBack) at Microsoft.TeamFoundation.TestManagement.Warehouse.TeamTestWarehouseAdapter.QueryForTestPoints(WarehouseResultDatabase wrd, SqlBinary waterMark, Int32 limit) at Microsoft.TeamFoundation.TestManagement.Warehouse.TeamTestWarehouseAdapter.ProcessIntegratedResults(WarehouseRowVersionQueryDelegate wqd, String highWaterMarkProperty, String processLimitProperty, Int32 defaultLimit) at Microsoft.TeamFoundation.TestManagement.Warehouse.TeamTestWarehouseAdapter.MakeDataChanges() at Microsoft.TeamFoundation.Warehouse.WarehouseSyncJobExtension1.MakeDataChanges(TeamFoundationRequestContext requestContext, TeamFoundationJobDefinition jobDefinition, String& resultMessage) at Microsoft.TeamFoundation.Warehouse.WarehouseSyncJobExtension1.RunInternal(TeamFoundationRequestContext requestContext, TeamFoundationJobDefinition jobDefinition, DateTime queueTime, String& resultMessage) at Microsoft.TeamFoundation.Warehouse.WarehouseJobExtension.Run(TeamFoundationRequestContext requestContext, TeamFoundationJobDefinition jobDefinition, DateTime queueTime, String& resultMessage) --- End of inner exception stack trace ---

Just TFS Just TFS · Accepted Answer · 2014-07-15T07:37:06

You can change the logging of the job agent by altering the TFSJobAgent.exe.config file. it can be found here:-

C:\Program Files\Microsoft Team Foundation Server 12.0\Application Tier\TFSJobAgent

 <system.diagnostics>
    <trace autoflush="false" indentsize="4">
      <!--To enable tracing to file, simply uncomment listeners section and set trace switch(es) below.
          Directory specified for TextWriterTraceListener output must exist, and job agent service account must have write permissions. -->
      <!--<listeners>
        <add name="myListener" 
          type="System.Diagnostics.TextWriterTraceListener" 
          initializeData="C:\Replace_Me_With_A_Directory_The_Service_Account_Can_Write_To\jobagent.log" />
        <remove name="Default" />
      </listeners>-->
    </trace>
    <switches>
      <!--  Trace Switches
            Each of the trace switches should be set to a value between 0 and 4, inclusive.
              0: No trace output
              1-4: Increasing levels of trace output; see Systems.Diagnostics.TraceLevel-->
      <add name="API" value="0" />
      <add name="Authentication" value="0" />
      <add name="Authorization" value="0" />
      <add name="Database" value="0" />
      <add name="General" value="0" />
      <add name="traceLevel" value="0" />
    </switches> 
   </system.diagnostics>

This may give you better diagnostics with which to investigate the issue

For timeout settings see http://msdn.microsoft.com/en-us/library/ff458305.aspx

TFS - Test Management Warehouse Sync - SQL Timeout

1 Answers