patternsqlModerate
Transaction Log Maintanance While Using AlwaysOn Availability Group
Viewed 0 times
whilegrouplogusingtransactionavailabilitymaintanancealwayson
Problem
We are using HADR ( AlwaysOn Availability Group) feature of SQL Server 2012.
Server and AG Configuration as below:
-
SQL12_SRV1
--> AG1(PRIMARY) - SYNC
-->> DBTest
-
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.
Server and AG Configuration as below:
-
SQL12_SRV1
--> AG1(PRIMARY) - SYNC
-->> DBTest
-
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.
Solution
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,
Now when I do a transaction log backup on my primary, using
I now do a transaction log backup on the secondary async replica. Running
BOL Reference: Active Secondaries: Backup on Secondary Replicas (AlwaysOn Availability Groups)
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;
go
create table dbo.TestTable
(
id int identity(1, 1) not null,
some_int int not null
default 1
);
go
insert into dbo.TestTable
default values;
go 1000Now 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 1000I 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)
Code Snippets
use TestBackupDatabase;
go
create table dbo.TestTable
(
id int identity(1, 1) not null,
some_int int not null
default 1
);
go
insert into dbo.TestTable
default values;
go 1000insert into dbo.TestTable
default values;
go 1000Context
StackExchange Database Administrators Q#44943, answer score: 10
Revisions (0)
No revisions yet.