Large Database Table Sync Issue

Jun 24, 2014 at 6:49 PM
I have a very large database that I was trying to sync down to my computer but it threw the error below. I have a number of questions about it. First, is the table sysdiagrams generated by the sync framework because I never found it in my database before I provisioned it. Also is there a way to remedy the problem below with either a change to the client or the server?
Microsoft.Synchronization.Data.DbSyncException
Cannot enumerate changes at the RelationalSyncProvider for table 'sysdiagrams'.  Check the inner exception for any store-specific errors.
   at Microsoft.Synchronization.Data.DbSyncBatchProducer.DequeueBatch()
   at Microsoft.Synchronization.Data.RelationalSyncProvider.ConsumeBatchFromProducer(DbSyncScopeMetadata scopeMetadata)
   at Microsoft.Synchronization.Data.RelationalSyncProvider.GetChanges(DbSyncScopeMetadata scopeMetadata, DbSyncSession DbSyncSession, UInt32 memoryBatchSize)
   at Microsoft.Synchronization.Data.RelationalSyncProvider.GetChangeBatch(UInt32 batchSize, SyncKnowledge destinationKnowledge, Object& changeDataRetriever)
   at Microsoft.Synchronization.Services.SqlProvider.SqlSyncProviderService.GetChanges(Byte[] serverBlob)
   at Microsoft.Synchronization.Services.DownloadChangesRequestProcessor.ProcessRequest(Request incomingRequest)
   at Microsoft.Synchronization.Services.SyncService`1.ProcessRequestForMessage(Stream messageBody)

Microsoft.Synchronization.Data.DbSyncException
The row of size 462KB from table 'sysdiagrams' cannot fit within the specified batch size of 204KB. Row ID values: diagram_id = 1. Consider increasing the value of RelationalSyncProvider.MemoryDataCacheSize for the source and destination providers.
   at Microsoft.Synchronization.Data.RelationalSyncProvider.ThrowRowTooBigForBatchException(IDataReader dataReader, DbDataReaderHandler readerHandler)
   at Microsoft.Synchronization.Data.RelationalSyncProvider.EnumerateChangesInBatchesInternal(Object batchProducer)
Coordinator
Jun 25, 2014 at 10:38 AM
No, sysdiagrams is created by SQL SERVER when you add a database diagram within your database (in SQL Server Management Studio)

You must not include this table in your Sync provisionning.
Jun 25, 2014 at 11:55 PM
Thanks, that was super helpful.
Nov 4, 2014 at 10:19 AM
Edited Nov 4, 2014 at 10:36 AM
Hi,

I'm get the same error message. How do I get a reference to increase MemoryDataCacheSize please?
Microsoft.Synchronization.Data.DbSyncException
Cannot enumerate changes at the RelationalSyncProvider for table 'Survey'.  Check the inner exception for any store-specific errors.
   at Microsoft.Synchronization.Data.DbSyncBatchProducer.DequeueBatch()
   at Microsoft.Synchronization.Data.RelationalSyncProvider.ConsumeBatchFromProducer(DbSyncScopeMetadata scopeMetadata)
   at Microsoft.Synchronization.Data.RelationalSyncProvider.GetChanges(DbSyncScopeMetadata scopeMetadata, DbSyncSession DbSyncSession, UInt32 memoryBatchSize)
   at Microsoft.Synchronization.Data.RelationalSyncProvider.GetChangeBatch(UInt32 batchSize, SyncKnowledge destinationKnowledge, Object& changeDataRetriever)
   at Microsoft.Synchronization.Services.SqlProvider.SqlSyncProviderService.GetChanges(Byte[] serverBlob)
   at Microsoft.Synchronization.Services.DownloadChangesRequestProcessor.ProcessRequest(Request incomingRequest)
   at Microsoft.Synchronization.Services.SyncService`1.ProcessRequestForMessage(Stream messageBody)

Microsoft.Synchronization.Data.DbSyncException
The row of size 428KB from table 'Survey' cannot fit within the specified batch size of 204KB. Row ID values: SurveyID = 1, Propref = 1234567890          , MachineName = G12345678977668, Date = 2014-10-29 13:16:50:474. Consider increasing the value of RelationalSyncProvider.MemoryDataCacheSize for the source and destination providers.
   at Microsoft.Synchronization.Data.RelationalSyncProvider.ThrowRowTooBigForBatchException(IDataReader dataReader, DbDataReaderHandler readerHandler)
   at Microsoft.Synchronization.Data.RelationalSyncProvider.EnumerateChangesInBatchesInternal(Object batchProducer)
Nov 5, 2014 at 10:19 AM
Hi,

It's OK I solved it though unsatisfactorily through the SetDownloadBatchSize property. Contrary to the name this doesn't appear to be about batching but overall size of a row of data allowed.
Nov 7, 2014 at 3:35 AM
That's what I found as well. As far as my program indicated a row had to fit inside of a batch and although there could be multiple rows per batch there couldn't be multiple batches per row. Our program eventually began limiting the size of the pictures that were being synced because they maxed out even the largest batch size. Best of luck.
Nov 7, 2014 at 11:03 AM
Hi,

Thanks for your response, nice explanation!

What size of picture have you found optimal and what sort of performance are you seeing when transferring. On the 8.1 (RT) tablet I'm developing for it's taking, roughly speaking, 700kb pictures, which are taking in the region of 40 seconds to sync over our intranet. This is obviously very poor and I'm trying to tweak IIS and the web.config for the service to improve this.

How are you transferring the pics? I'm storing them in a db server side, so within this framework they are held as byte arrays which are then serialized via JSON, I'm concerned it may be the resulting large JSON file that could be responsible for the poor performance.

I'd be very interested to hear your approach.

Thanks for your time
Nov 14, 2014 at 9:51 AM
Just an update for anyone who comes across this post.

I changed the serialization type to atom instead of JSON and the sync happens in less than a second. I don't know if there is an issue serializing a byte array into JSON or whether IIS 7.5 struggles with JSON or if it's some other config issue but this has certainly worked for me.