locked
"Unable to enumerate changes" error RRS feed

  • Question

  • SyncFramework 2.x syncing SQLCE on NetBook to SQL Server 2008 over WCF.

    I have a sync application in production that works 98% of the time.  Occasionally while trying to sync a .sdf file a user gets the "unable to enumerate changes" error ... and the error always specifies the last table in the sync (remember 98% of the time this table syncs okay...).  If I temporarily remove this last table from the sync, the sync finishes successfully.

    The error is thrown by GetChanges() of the SyncProvider.  Once the error manifests itself - the user gets it every sync attempt.  The fix is to create a new .sdf file for the user (which theoretically has the same data in it as the problematic .sdf) and then the syncs work fine again.

    I captured the WCF events to a trace log and the genesis of the error appears to be reported as:

    "System.Data.SqlClient.SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

    I captured the actual SQL statement in SQL Profiler ... I can cut/paste the statement into SSMS and it executes immediately returning the expected ~600 records.  The profiler says the statement ran for 1 minute before failing (because I increased the sql command.CommandTimeout to 60 from I think a default of 30).

    Any ideas on how i can get a better picture of what is going on?  I don't understand how SSMS returns an immediate result but the Profiler reports one minute to process the command (then apparently fails).

    Thursday, March 24, 2011 8:10 PM

Answers

  • Resolved ...

    apparently my sqlCommand.CommandTimeout  of 02:00 was right on the threshold ... 02:30 seems to do the trick.

    Now I have to make my query more efficient and back that timeout value down.

    Thanks.

    Wednesday, March 30, 2011 10:24 PM

All replies

  • Here is the relevant section from the trace log:

    <E2ETraceEvent xmlns="http://schemas.microsoft.com/2004/06/E2ETraceEvent">

    <System xmlns="http://schemas.microsoft.com/2004/06/windows/eventlog/system">

    <EventID>131076</EventID>

    <Type>3</Type>

    <SubType Name="Error">0</SubType>

    <Level>2</Level>

    <TimeCreated SystemTime="2011-03-24T17:42:19.8230595Z" />

    <Source Name="System.ServiceModel" />

    <Correlation ActivityID="{f3c38403-63dc-4280-962a-8cebe422034b}" />

    <Execution ProcessName="w3wp" ProcessID="3876" ThreadID="12" />

    <Channel />

    <Computer>HCSSRV-05</Computer>

    </System>

    <ApplicationData>

    <TraceData>

    <DataItem>

    <TraceRecord xmlns="http://schemas.microsoft.com/2004/10/E2ETraceEvent/TraceRecord" Severity="Error">

    <TraceIdentifier>http://msdn.microsoft.com/en-US/library/System.ServiceModel.Diagnostics.TraceHandledException.aspx</TraceIdentifier>

    <Description>Handling an exception.</Description>

    <AppDomain>/LM/W3SVC/9/ROOT/CCHH_Beta-3-129454620668300955</AppDomain>

    <Exception>

    <ExceptionType>Microsoft.Synchronization.Data.SyncException, Microsoft.Synchronization.Data, Version=1.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91</ExceptionType>

    <Message>Unable to enumerate changes at the DbServerSyncProvider for table 'POCMaint_SyncTableIdReference' in synchronization group 'SYNC HISTORY'.</Message>

    <StackTrace>

    at Microsoft.Synchronization.Data.Server.DbServerSyncProvider.EnumerateChanges(SyncGroupMetadata groupMetadata, SyncSession syncSession, IDbTransaction transaction, EnumerateChangeType changeType, SyncSchema traceSchema)

    at Microsoft.Synchronization.Data.Server.DbServerSyncProvider.GetChanges(SyncGroupMetadata groupMetadata, SyncSession syncSession)

    at SyncServiceLibrary.LocalDataCacheSyncService.GetChanges(SyncGroupMetadata groupMetadata, SyncSession syncSession) in C:\Visual Studio 2008 Projects\ConnectPOC\SyncServiceLibrary\LocalDataCache.Server.SyncContract.cs:line 52

    at SyncInvokeGetChanges(Object , Object[] , Object[] )

    at System.ServiceModel.Dispatcher.SyncMethodInvoker.Invoke(Object instance, Object[] inputs, Object[]&amp; outputs)

    at System.ServiceModel.Dispatcher.DispatchOperationRuntime.InvokeBegin(MessageRpc&amp; rpc)

    at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage5(MessageRpc&amp; rpc)

    at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage41(MessageRpc&amp; rpc)

    at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage4(MessageRpc&amp; rpc)

    at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage31(MessageRpc&amp; rpc)

    at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage3(MessageRpc&amp; rpc)

    at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage2(MessageRpc&amp; rpc)

    at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage11(MessageRpc&amp; rpc)

    at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage1(MessageRpc&amp; rpc)

    at System.ServiceModel.Dispatcher.MessageRpc.Process(Boolean isOperationContextSet)

    </StackTrace>

    <ExceptionString>Microsoft.Synchronization.Data.SyncException: Unable to enumerate changes at the DbServerSyncProvider for table 'POCMaint_SyncTableIdReference' in synchronization group 'SYNC HISTORY'. ---&gt; System.Data.SqlClient.SqlException: 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()

    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.ExecuteDbDataReader(CommandBehavior behavior)

    at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()

    at Microsoft.Synchronization.Data.Server.DbServerSyncProvider.EnumerateChanges(SyncGroupMetadata groupMetadata, SyncSession syncSession, IDbTransaction transaction, EnumerateChangeType changeType, SyncSchema traceSchema)

    --- End of inner exception stack trace ---

    at Microsoft.Synchronization.Data.Server.DbServerSyncProvider.EnumerateChanges(SyncGroupMetadata groupMetadata, SyncSession syncSession, IDbTransaction transaction, EnumerateChangeType changeType, SyncSchema traceSchema)

    at Microsoft.Synchronization.Data.Server.DbServerSyncProvider.GetChanges(SyncGroupMetadata groupMetadata, SyncSession syncSession)

    at SyncServiceLibrary.LocalDataCacheSyncService.GetChanges(SyncGroupMetadata groupMetadata, SyncSession syncSession) in C:\Visual Studio 2008 Projects\ConnectPOC\SyncServiceLibrary\LocalDataCache.Server.SyncContract.cs:line 52

    at SyncInvokeGetChanges(Object , Object[] , Object[] )

    at System.ServiceModel.Dispatcher.SyncMethodInvoker.Invoke(Object instance, Object[] inputs, Object[]&amp; outputs)

    at System.ServiceModel.Dispatcher.DispatchOperationRuntime.InvokeBegin(MessageRpc&amp; rpc)

    at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage5(MessageRpc&amp; rpc)

    at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage41(MessageRpc&amp; rpc)

    at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage4(MessageRpc&amp; rpc)

    at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage31(MessageRpc&amp; rpc)

    at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage3(MessageRpc&amp; rpc)

    at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage2(MessageRpc&amp; rpc)

    at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage11(MessageRpc&amp; rpc)

    at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage1(MessageRpc&amp; rpc)

    at System.ServiceModel.Dispatcher.MessageRpc.Process(Boolean isOperationContextSet)</ExceptionString>

    <InnerException>

    <ExceptionType>System.Data.SqlClient.SqlException, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</ExceptionType>

    <Message>Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.</Message>

    <StackTrace>

    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()

    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.ExecuteDbDataReader(CommandBehavior behavior)

    at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()

    at Microsoft.Synchronization.Data.Server.DbServerSyncProvider.EnumerateChanges(SyncGroupMetadata groupMetadata, SyncSession syncSession, IDbTransaction transaction, EnumerateChangeType changeType, SyncSchema traceSchema)

    </StackTrace>

    <ExceptionString>System.Data.SqlClient.SqlException (0x80131904): 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()

    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.ExecuteDbDataReader(CommandBehavior behavior)

    at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()

    at Microsoft.Synchronization.Data.Server.DbServerSyncProvider.EnumerateChanges(SyncGroupMetadata groupMetadata, SyncSession syncSession, IDbTransaction transaction, EnumerateChangeType changeType, SyncSchema traceSchema)</ExceptionString>

    <DataItems>

    <Data>

    <Key>HelpLink.ProdName</Key>

    <Value>Microsoft SQL Server</Value>

    </Data>

    <Data>

    <Key>HelpLink.ProdVer</Key>

    <Value>10.00.1600</Value>

    </Data>

    <Data>

    <Key>HelpLink.EvtSrc</Key>

    <Value>MSSQLServer</Value>

    </Data>

    <Data>

    <Key>HelpLink.EvtID</Key>

    <Value>-2</Value>

    </Data>

    <Data>

    <Key>HelpLink.BaseHelpUrl</Key>

    <Value>http://go.microsoft.com/fwlink</Value>

    </Data>

    <Data>

    <Key>HelpLink.LinkId</Key>

    <Value>20476</Value>

    </Data>

    </DataItems>

    </InnerException>

    </Exception>

    </TraceRecord>

    </DataItem>

    </TraceData>

    </ApplicationData>

    </E2ETraceEvent>

     


    Thursday, March 24, 2011 8:11 PM
  • are there any other applications accessing the same table at the same time? check using SSMS Activity Monitor or sp_who if there are any processes blocking the sync fx queries.
    Thursday, March 24, 2011 11:25 PM
  • Thanks for the suggestion!

    Yes there are other apps accessing the same tables.  I have watched the Activity Monitor and have not observed any blocking - I guess I could increase the sqlCommand.CommandTimeout to something ridiculous like 10 minutes to see if maybe a blocking issue might be more easily detected ... before the block clears itself.  Yesterday I increased the timeout from 1 min to 2 mins ... but 10 might give me a better chance to see a block.

    Friday, March 25, 2011 1:18 PM
  • Resolved ...

    apparently my sqlCommand.CommandTimeout  of 02:00 was right on the threshold ... 02:30 seems to do the trick.

    Now I have to make my query more efficient and back that timeout value down.

    Thanks.

    Wednesday, March 30, 2011 10:24 PM