DataSync/SQL Limitation?

Question

We recently received this error performing a Data Sync configured in the managent console:

Sync failed with the exception "Knowledge size is <x> bytes which is greater than the maximum supported value of 10485760 bytes"

What conditions lead to this exception?  Can provide any details on request.

Thank you,

DustenSalinas

As suggested by JuneT here is additional information including trace ids:

The exception is occuring between sql azure db's, uni-directional sync from hub (wins).  Roughly 5 million rows, insert, update and delete (updates rare). 

TraceIds: 20f30bda-5780-4e78-8f54-afbb2c5ec6a, 74ea4920-4cc8-4db9-9cfb-61e96a6b62ec, 690b0c5c-d722-4bf1-b04b-6527cced3cfe

As memory serves, SSIS has a default buffer of 10megs but I refrain from jumping to conclusions.

Answer 1

you might want to post the tracing id just in case a member of the team logs in here so they can take a look at the logs.

is the sync between sql azure DBs? or Azure DBs and on-premise DBs? number of rows?

Answer 2

@Dustens,

The data flow task in SSIS sends data in series of buffers. The data one buffer hold is bounded by Default BufferMaxRows and DefaultBufferMaxSize, two Data Flow properties. They have default values of 10,000 and 10,485,760 (10 MB). That means, one buffer will contain either 10,000 rows or 10 MB of data, whichever is less. You can adjust these two properties based on your scenario. Setting them to a higher value can boost performance, but only as long as all buffers fit in memory.

---

Best Regards,
Peja

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Answer 3

SQL Azure Data Sync Service don't use SSIS. There is limit of 5M rows and a 10mb row size in the current release.

but your error is on the sync knowledge itself (sync knowledge stores what was synched)  and not the actual data being synched.

the product team will be best to answer what's going on.

Answer 4

Hi Dustens,

Do you have filters defined on the sync group? Sync knowledge could become large if the filter causes a table to become fragmented and knowledge could not be efficiently compacted.

Can you run the following query and reply with the distribution of the sizes of sync knowledge in your databases?

select len(scope_sync_knowledge) from DataSync.scope_info_dss

Thanks,

Minh.

Answer 5

Hi Dustens,

Do you have filters defined on the sync group? Sync knowledge could become large if the filter causes a table to become fragmented and knowledge could not be efficiently compacted.

Can you run the following query and reply with the distribution of the sizes of sync knowledge in your databases?

select len(scope_sync_knowledge) from DataSync.scope_info_dss

Thanks,

Minh.

Hello Minh,

Indeed we did have a filter on a specific GUID primary key.  We've since reworked the datasync on the specific database that was receiving the issue and are no longer encountering it. Running the query now yields: 27590.  Thank you for the information, we'll keep an eye on it for the future.

Thank you everyone for your responses,

Dusten