0

I have this question on the generation of transaction logs. For a high transaction active DB what is the ratio of log generation if it is a standalone DB and when it is part of SQL Alwayson availability group(synchronous commit).

I am pretty sure it will be high, but how much higher. Rather than doing a test on my own any answer or reference will be helpful.

Note: The perfmon counter do show the numbers for transactions/logs flushed which seems to be really in case of AG. I want to understand if there is a way to limit it as well.

2

For a high transaction active DB what is the ratio of log generation if it is a standalone DB and when it is part of SQL Alwayson availability group(synchronous commit).

Assuming Full Recovery mode, which AGs require, 1:1. AGs don't require any additional logging beyond what a Full Recovery database already does.

In Simple recovery mode you might have some minimally logged operations, but for a "high transaction active" database, it's unlikely. See eg

SQL Server 2016, Minimal logging and Impact of the Batchsize in bulk load operations

  • Thanks David, Just did a test to validate and its the same. I was under an assumption the synchronous AG will track more activity for the DB on behalf of other replicas. Hence was the doubt. – Linu1988 Aug 3 '18 at 2:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.