Transaction Log Backups on Availability databases and its effect on database shrink

With the introduction of availability databases, I have been queried frequently about how to schedule a T-Log backup on Availability databases, do we need to schedule the backup on both the servers or only on Primary. To clear these doubts I planned to do some tests to explain the effect of T-Log backup on Availability databases. The architecture of T-Log has been clearly explained with respect to Availability databases in the below article, All I am going to do is do some testing to prove this.

http://blogs.msdn.com/b/alwaysonpro/archive/2013/09/27/performing-transaction-log-backups-using-alwayson-availability-group-read-only-secondary-replicas-part-1.aspx

I am going to use a availability database called AGTest for this testing,

Create the test Table on Primary database,

USE [AGTest]

GO

CREATE TABLE [dbo].[TLogTest](

       [Cnt] [int] NULL

) ON [PRIMARY]

GO

Get the T-Log usage before data updates

DBCC SQLPERF(LOGSPACE)

Primary Database:-

Database Name	Log Size (MB)	Log Space Used (%)	Status
AGTest	0.9921875	37.20472	0

Secondary HA Database:-

Database Name	Log Size (MB)	Log Space Used (%)	Status
AGTest	0.9921875	12.79528	0

Do a logged operation on Primary database for T-Log usage,

DECLARE @intLimit AS INT = 1000000

DECLARE @intCounter AS INT = 1

WHILE @intCounter <= @intLimit

BEGIN

INSERT INTO TLogTest VALUES (@intCounter)

SET @intCounter = @intCounter + 1

END

Get the T-Log usage after logged operation

Primary Database:-

Database Name	Log Size (MB)	Log Space Used (%)	Status
AGTest	51.17969	98.73397	0

Secondary HA Database:-

Database Name	Log Size (MB)	Log Space Used (%)	Status
AGTest	51.17969	98.21497	0

The above metric looks logical as per the HA architecture, now I am going to Take T-Log backup of the AGTest Database in Primary alone,

BACKUP LOG [AGTest] TO DISK = 't:\Test.bkp'

Now let’s look at the T-Log size,

Primary Database:-

Database Name	Log Size (MB)	Log Space Used (%)	Status
AGTest	51.17969	3.325828	0

Secondary HA Database:-

Database Name	Log Size (MB)	Log Space Used (%)	Status
AGTest	51.17969	2.814456	0

So the a T-Log backup on the primary does truncate the log records on both Primary and the secondary HA database. Technically backup on either of the database should be enough but there is a catch here, Now I try to shrink the T-Log on primary let’s see what happens.

Now let’s look at the Log File size,

Primary Database:-

Database Name	Log Size (MB)	Log Space Used (%)	Status
AGTest	51.17969	6.031522	0

Secondary HA Database:-

Database Name	Log Size (MB)	Log Space Used (%)	Status
AGTest	51.17969	5.566898	0

Whatever I try to do I was not able to shrink the file, Now I decided to the only step I missed BACKUP Secondary HA database.

BACKUP LOG [AGTest] TO DISK = 'P:\Test.bkp'

Now I ran the shrink again on the primary database, Let’s see the Log file size now,

Primary Database:-

Database Name	Log Size (MB)	Log Space Used (%)	Status
AGTest	0.9921875	30.01969	0

Secondary HA Database:-

Database Name	Log Size (MB)	Log Space Used (%)	Status
AGTest	0.9921875	5.659449	0

:-) And there it goes log file was shrunk in both Primary and secondary databases. Hope this article gives you an idea of how to schedule a log backup on HA databases.