none
DataSyncException - timeout problems RRS feed

  • Question

  • Hi,

    I need some help as we face a major problem in our sync. We have timeouts occuring between Sync webservice and SQL Server leading to fatal aborts.

    We use ADO SyncServices 1 SP1 for devices as we need to support Windows Mobile 6.1 clients. On the server side we have an ASMX webservice using the DbServerSyncProvider, database is SQL 2008 R2 (CU7, 10.50.1777).
    The sync queries are custom built (we need custom joins for filtering) and our laid out in stored procedures. They make heavy use of SQL 2008 changetracking feature.

    We see that there are performance problems with changetracking that lead to long execution times in our SPs.
    (Please also see http://social.msdn.microsoft.com/Forums/en/sqlreplication/thread/897660f9-5f98-4842-862e-3bf798862276)
    When a critical point is reached (~30s for single incrementalInsert query), the sync progress gets aborted.
    We are in trying to rework the queries but most of the changetracking stuff is internal and cannot be changed.

    Nevertheless I hope that you can help me to locate and prevent the timeout to occur. Better slow data than no data!
    The timeout of the SqlConnection of the DbServerSyncProvider is set to "0".

    The exception occurs at the GetChanges(groupMetadata, syncSession) method in DbServerSyncProvider.

    ex: Microsoft.Synchronization.Data.DataSyncException
    message (translated): changes for table xyz in the sync group xyz can not be enumerated at the DbServerSyncProvider
    source: Microsoft.Synchronization.Data.Server
    inner ex: System.Data.SqlClient.SqlException
    inner message: timeout occured before process finished or server is not responding
    stack:
       bei Microsoft.Synchronization.Data.Server.DbServerSyncProvider.EnumerateChanges(SyncGroupMetadata groupMetadata, SyncSession syncSession, IDbTransaction transaction, EnumerateChangeType changeType, SyncSchema traceSchema)
       bei Microsoft.Synchronization.Data.Server.DbServerSyncProvider.GetChanges(SyncGroupMetadata groupMetadata, SyncSession syncSession)
       bei mynamespace.SyncService.GetChanges(SyncGroupMetadata groupMetadata, SyncSession syncSession) in C:\<>\SyncService.asmx.cs:Zeile 530.

    Any hints are well appreciated,

    Andreas

    Tuesday, June 21, 2011 8:14 AM

Answers

  • try something like this to set the command timeout on the individual commands for each table or you can do this just on the problem table:

    LocalDataCache1ServerSyncProvider remoteProvider = (LocalDataCache1ServerSyncProvider)syncAgent.RemoteProvider;
    
    IDbCommand selectIncrementalInsertsCommand = remoteProvider.SalesLT_CustomerSyncAdapter.SelectIncrementalInsertsCommand;
    IDbCommand selectIncrementalUpdatesCommand = remoteProvider.SalesLT_CustomerSyncAdapter.SelectIncrementalUpdatesCommand;
    IDbCommand selectIncrementalDeletesCommand = remoteProvider.SalesLT_CustomerSyncAdapter.SelectIncrementalDeletesCommand;
    
    selectIncrementalInsertsCommand.CommandTimeout = 60; //replace with your preferred time out value
    selectIncrementalUpdatesCommand.CommandTimeout = 60;
    selectIncrementalDeletesCommand.CommandTimeout = 60;
    
    remoteProvider.SalesLT_CustomerSyncAdapter.SelectIncrementalInsertsCommand = selectIncrementalInsertsCommand;
    remoteProvider.SalesLT_CustomerSyncAdapter.SelectIncrementalDeletesCommand = selectIncrementalDeletesCommand;
    remoteProvider.SalesLT_CustomerSyncAdapter.SelectIncrementalUpdatesCommand = selectIncrementalUpdatesCommand;
    
    

    you're basically retrieving the command object for each table, then just adding a timeout then assigning it back.

    SalesLT_CustomerSyncAdapter should be replaced with the specific adapter (table) you want to specify a timeout.


    Tuesday, June 21, 2011 8:47 AM
    Moderator

All replies

  • try something like this to set the command timeout on the individual commands for each table or you can do this just on the problem table:

    LocalDataCache1ServerSyncProvider remoteProvider = (LocalDataCache1ServerSyncProvider)syncAgent.RemoteProvider;
    
    IDbCommand selectIncrementalInsertsCommand = remoteProvider.SalesLT_CustomerSyncAdapter.SelectIncrementalInsertsCommand;
    IDbCommand selectIncrementalUpdatesCommand = remoteProvider.SalesLT_CustomerSyncAdapter.SelectIncrementalUpdatesCommand;
    IDbCommand selectIncrementalDeletesCommand = remoteProvider.SalesLT_CustomerSyncAdapter.SelectIncrementalDeletesCommand;
    
    selectIncrementalInsertsCommand.CommandTimeout = 60; //replace with your preferred time out value
    selectIncrementalUpdatesCommand.CommandTimeout = 60;
    selectIncrementalDeletesCommand.CommandTimeout = 60;
    
    remoteProvider.SalesLT_CustomerSyncAdapter.SelectIncrementalInsertsCommand = selectIncrementalInsertsCommand;
    remoteProvider.SalesLT_CustomerSyncAdapter.SelectIncrementalDeletesCommand = selectIncrementalDeletesCommand;
    remoteProvider.SalesLT_CustomerSyncAdapter.SelectIncrementalUpdatesCommand = selectIncrementalUpdatesCommand;
    
    

    you're basically retrieving the command object for each table, then just adding a timeout then assigning it back.

    SalesLT_CustomerSyncAdapter should be replaced with the specific adapter (table) you want to specify a timeout.


    Tuesday, June 21, 2011 8:47 AM
    Moderator
  • Hello JuneT,

    thanks for the suggestion. We had the CommandTimeouts set to "0" some refactorings before which seemed to net solve this.

    I set it to fixed values again after reading this and until now it works out for us.

    Thanks!

    Friday, June 24, 2011 10:06 AM