Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

We are using HADR ( AlwaysOn Availability Group) feature of SQL Server 2012. Server and AG Configuration as below:

  1. SQL12_SRV1 --> AG1(PRIMARY) - SYNC -->> DBTest

  2. SQL12_SRV2 --> AG1(SECONDARY) -->> DBTest - ASYNC

DBTest Database is growing (200GB) Day to day approximate monthly and same Transaction Log File will also grow according to Data.

So How to minimize Transaction Log File Size by using proper way of taking LOG backup. On which Replica we have to take log backup.

Thanks In Advance.

share|improve this question

You can backup the transaction log from either of the replicas. Doing the transaction log backup on either the primary replica or the secondary replica will mark both replicas' transaction logs as reusable (provided no other stoppers are in place like active transactions, etc.).

To do a test, in a non-production environment, setup an availability group just as you have it in your production system (asynchronous commit to the secondary replica).

In my test environment I have a test database, TestBackupDatabase, and I bloated it with logged transactions through a dummy table:

use TestBackupDatabase;

create table dbo.TestTable
    id int identity(1, 1) not null,
    some_int int not null
        default 1

insert into dbo.TestTable
default values;
go 1000

Now when I do a transaction log backup on my primary, using DBCC SQLPERF(LOGSPACE) I see on both transaction logs (primary and secondary) that space used has dropped due to log truncation. Bloating the transaction log back up with the same test on the primary:

insert into dbo.TestTable
default values;
go 1000

I now do a transaction log backup on the secondary async replica. Running DBCC SQLPERF(LOGSPACE) again on each replica I see the same behavior: transaction log reuse.

BOL Reference: Active Secondaries: Backup on Secondary Replicas (AlwaysOn Availability Groups)

share|improve this answer

When you use AlwaysOn Availability Group, even if you take a proper backup the log file might grow larger and the log drive might get full over time. in order to maintain proper (minimize) log file size you can use the following technique.

  1. on the AlwaysOn configuration, change the backup priority options to primary replica/server.
  2. Since the databases are by default on a full recovery mode, take at least one a full backup and 1 Take a transaction log backup.
  3. Shrink the log files of all databases on primary replica. This will truncate the empty the log drives on all availability replicas.
  4. Finally, schedule a job to take appropriate backup on a regular basis. This will keep you log file on the right size.

I hope this will help!

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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