what determines transaction size (split transactions)

israelc

I am using SQL Data Compare 8.1.0.4. When generating a synchronization script, I specified a 2 MB transaction size limit to generate split transactions. Because the database has a lot of binary data, the resulting script is 225 MB in size (on disk). I expected to see multiple begin-transaction and commit statements, but I only found one begin-transaction in the beginning and one commit at the end, bounding all SQL INSERT statements. What determines transaction size, as far as the split-transaction feature is concerned?

Brian Donahue

Thanks for your question. Your assumprions about transaction splitting are correct. When the script is being created, the individual batches of SQL (a batch is everything between the GO statements) have their size calculated and when this reaches 2MB, you should see a COMMIT TRANSACTION and another BEGIN TRANSACTION. Of course, it will not break up any large updates involving a single BLOB.

If you are using the command-line version of Data Compare, then you need the cumulative patch to version 8.1.1 to get support for splitting.

If you absolutely can't work out why it's not splitting correctly, feel free to contact support via email.