none
"An unexpected error occurred when applying batch file" while syncing between two sql server RRS feed

  • Question

  • I'm using SqlSyncProvider to sync between SQL 2008 Express and 2005 in 2-tier scenarios,

    when I upload a table with large amount of data(about 2.3GB) always cause the following exception. localProvider.MemoryDataCacheSize was set to 20480 .

    Microsoft.Synchronization.Data.DbSyncException: An unexpected error occurred when applying batch file E:\DataSync\Bin\sync_f0ce1a843cdc4ae088282d685ef45beaa33764489fbc42c780e1dc005999e098\a5d9ec4f-7295-4808-b870-02aaef9df7fb.batch. See the inner exception for more details.

    ---> Microsoft.Synchronization.Data.DbSyncException: Failed to execute the command 'InsertCommand' for table 'tTable1'; the transaction was rolled back. Ensure that the command syntax is correct.

    ---> System.Data.SqlClient.SqlException: Timeout expired.   The timeout period elapsed prior to completion of the operation or the server is not responding.

    Sunday, May 16, 2010 9:22 AM

Answers

  • Hi,

    Based on the information you provided, I started to feel that it may not be a bug in the database providers. With Sync Framework 2.0 RTM, all changes are applied in one transaction, and each apply insert is in a separate SqlCommand. With 2.3 G of changes, your transaction logs will grow huge, and the SQL Server insert will become slower and slower. I guess eventually the 30 seconds default SqlCommand.CommandTimeout is not enough for insert one row anymore. Since about 1.5G of changes can be synced before hitting this exception, can you divide your databases to multiple sync scopes, and each sync scope only contain a subset of tables in the database? If you sync these scopes one-by-one, I think that you will succeed because the data in each scope is much smaller. If you only have one big table in the database, please consider using filter to create multiple sync scopes. You can find more details for how to use filter in this MSDN link: http://msdn.microsoft.com/en-us/library/dd918848(v=SQL.105).aspx.

    Thanks,
    Dong


    This posting is provided AS IS with no warranties, and confers no rights.
    Tuesday, May 18, 2010 6:48 PM
    Moderator

All replies

  • 2.3GB is a lot of data for an upload and you're uploading in 20mb chunks. something to note, when batching, the batch determines the size of the upload and although you have multiple batches, all the changes are actually applied in a single transaction. so you're 2.3GB worth of changes is applied as a single transaction.

    have you tried to increase your SQL timeout?

    Monday, May 17, 2010 10:31 AM
    Moderator
  • Hi JuneT,

    Thanks for your reply.  I don't know how to increase the SQL timeout. I have two big database with about 10GB data, and both required to upload

    to a sql server data center. I can use backup and restore, then run PostRestoreFixup to init-sync one,  but how can I deal with the another one.

    If add more databases, how to deal with it?

     

    Thanks!

    Monday, May 17, 2010 11:28 AM
  • Hi,

    InsertCommand only inserts one row each time, and you should not hit SqlCommand Timeout. May I know how many batches have been applied when you hit this exception?

    Thanks,
    Dong


    This posting is provided AS IS with no warranties, and confers no rights.
    Monday, May 17, 2010 7:42 PM
    Moderator
  • About 150 batch files have been applied when I hit this exception, each batch file is about 10M.

    After encounter this exception, the server was not responding for about 5 minutes,  the sync jobs during this period always throw the following exception,

    about 5 minutes later, the server responds again.

     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(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 System.Data.SqlClient.SqlCommand.DeriveParameters()
       at System.Data.SqlClient.SqlCommandBuilder.DeriveParameters(SqlCommand command)
       at Microsoft.Synchronization.Data.ManagementUtils.GetCommandFromProcName(String procName, SqlConnection connection, IEnumerable`1 columns)
       at Microsoft.Synchronization.Data.SqlServer.SqlSyncProviderAdapterConfiguration.BuildAdapter(SqlConnection connection)
       at Microsoft.Synchronization.Data.SqlServer.SqlSyncProvider.Configure()
       at Microsoft.Synchronization.Data.SqlServer.SqlSyncProvider.BeginSession(SyncProviderPosition position, SyncSessionContext syncSessionContext)
       at Microsoft.Synchronization.KnowledgeProviderProxy.BeginSession(SYNC_PROVIDER_ROLE providerRole, ISyncSessionState pSessionState)
       at Microsoft.Synchronization.CoreInterop.ISyncSession.Start(CONFLICT_RESOLUTION_POLICY resolutionPolicy, _SYNC_SESSION_STATISTICS& pSyncSessionStatistics)
       at Microsoft.Synchronization.KnowledgeSyncOrchestrator.DoOneWaySyncHelper(SyncIdFormatGroup sourceIdFormats, SyncIdFormatGroup destinationIdFormats, KnowledgeSyncProviderConfiguration destinationConfiguration, SyncCallbacks DestinationCallbacks, ISyncProvider sourceProxy, ISyncProvider destinationProxy, ChangeDataAdapter callbackChangeDataAdapter, SyncDataConverter conflictDataConverter, Int32& changesApplied, Int32& changesFailed)
       at Microsoft.Synchronization.KnowledgeSyncOrchestrator.DoOneWayKnowledgeSync(SyncDataConverter sourceConverter, SyncDataConverter destinationConverter, SyncProvider sourceProvider, SyncProvider destinationProvider, Int32& changesApplied, Int32& changesFailed)
       at Microsoft.Synchronization.KnowledgeSyncOrchestrator.Synchronize()
       at Microsoft.Synchronization.SyncOrchestrator.Synchronize()

    Tuesday, May 18, 2010 2:39 AM
  • Hi,

    Based on the information you provided, I started to feel that it may not be a bug in the database providers. With Sync Framework 2.0 RTM, all changes are applied in one transaction, and each apply insert is in a separate SqlCommand. With 2.3 G of changes, your transaction logs will grow huge, and the SQL Server insert will become slower and slower. I guess eventually the 30 seconds default SqlCommand.CommandTimeout is not enough for insert one row anymore. Since about 1.5G of changes can be synced before hitting this exception, can you divide your databases to multiple sync scopes, and each sync scope only contain a subset of tables in the database? If you sync these scopes one-by-one, I think that you will succeed because the data in each scope is much smaller. If you only have one big table in the database, please consider using filter to create multiple sync scopes. You can find more details for how to use filter in this MSDN link: http://msdn.microsoft.com/en-us/library/dd918848(v=SQL.105).aspx.

    Thanks,
    Dong


    This posting is provided AS IS with no warranties, and confers no rights.
    Tuesday, May 18, 2010 6:48 PM
    Moderator