Sql-server – Shrink Transaction Log While Using AlwaysOn Availability Group 2014

availability-groupssql serversql server 2014transaction-log

I'm running a SQL Server 2014 Availability Group with a single secondary in asynchronous mode.

My logs in all the Availability Group databases keep growing and the built-in shrink tool only takes them down a notch.

Can anyone recommend a set of troubleshooting steps to find out why the logs keep growing or how to automate log shrink in Availability Group databases?

Best 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 (shrink) log file size you can use the following technique.

On the AlwaysOn configuration, change the backup priority options to primary replica/server. Since the databases are by default in a full recovery mode, take at least one a full and one transaction log backup. Shrink the log files of all databases on primary replica. This will truncate the empty the log drives on all availability replicas. 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!