Re: CommitBatchSize vs. CommitBatchThreshold for transactional distrib

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

I re-ran my tests and I am always seeing CommitBatchThreshold + 1
commands delivered to the subscriber. Maybe someone at Microsoft who
reads this can chime in on why this is happening, otherwise it sounds
like a bug to me. Not a showstopper, of course, but still not what is
expected based on documentation.

I also confirmed the bit about transactional boundries; I had
CommitBatchThreshold set to 5 and CommitBatchSize set to 1,000, then I
ran a transaction (one the publisher) where I update 10 rows one at a
time and commit. In Profiler I see a single transaction with 10
updates. When I run the 10 updates outside of a transaction on the
publisher I see transactions with 6 and 4 updates at the subscriber.

Unfortunately I don't think the concept of transaction vs. command is
very clearly explained either, since a command is really autocommit
transaction. It would probably cause less confusion to explain how the
values work if it's phrased more in terms of explicit vs. autocommit
transactions.

Thanks for your help,
Kendal Van Dyke
http://kendalvandyke.blogspot.com/


On Oct 24, 10:15 pm, Hilary Cotter <hilary.cot...@xxxxxxxxx> wrote:
commit batch size is the number of transactions before a commit, commit
batch threshold is the total number of commands before a commit. These
commands could be in different transactions, so its the lesser of these
values which will trip a commit.

However what is key to remember in this case is that the distribution agent
always honors transactional boundaries.

The only problem with your tests is test #3. If it was 5 and 2 it would make
more sense. I am wondering if perhaps they was a gap between the first 6 and
the last one.


.

Relevant Pages

  • Re: Theoretical Basis for SELECT FOR UPDATE
    ... It make the entire transaction ... I entitled to expect to commit here? ... Notice that I only say comma separated updates have the capability to do ... expressed as atomic operations, it would only solve my problem if it ...
    (comp.databases.theory)
  • Re: slow read in a transaction
    ... job for instance that creates a file within the context of a transaction. ... > object and hit the DB to execute a Stored Procedure. ... > generate the file I commit the transaction. ... > If I isolate the inserts and updates into a separate transaction in the SP ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Distrib agent question
    ... I have 1 transaction with 100,000 commands.. ... Do you mean it will commit 1000 commands at a time.. ... > Looking for a SQL Server replication book? ...
    (microsoft.public.sqlserver.replication)
  • Re: Distrib agent question
    ... one commit, no matter how many commands per transaction. ... >> Looking for a SQL Server replication book? ...
    (microsoft.public.sqlserver.replication)
  • slow read in a transaction
    ... There is a transaction that I start from within my C# code on a Connection ... object and hit the DB to execute a Stored Procedure. ... generate the file I commit the transaction. ... If I isolate the inserts and updates into a separate transaction in the SP ...
    (microsoft.public.dotnet.framework.adonet)