SQL SERVER – Huge Transaction Log (LDF) for Database in Availability Group

One of my blog readers posted on Facebook asked following question about Huge Transaction Log:

Hi Pinal,
We are using AlwaysOn availability group for our production database. The database is fairly large in size as its around 260 GB.  The database transaction log file grows around 2.5gb every day. Over the weekend the DBA team noticed that the Log file for this database bloated up to around 230 GB and then seems to hold steady. While troubleshooting I noted an error that the rebuild Index failed due to “Availability_Replica”. Here is the error which we saw in ERRORLOG

Error: 9002, Severity: 17, State: 9.
The transaction log for database is full due to ‘AVAILABILITY_REPLICA’

The question I have is what would be causing this log file to get so large, and is this normal behavior for a database in an Availability Group?

Thanks!

Here is my reply to him:

Thanks for asking such a wonderful question. Before I answer the questions, keep below in mind:

1. Make sure that regular transaction log backups are taken to make sure it’s getting truncated on a regular basis. This is needed for any database which is in full or bulk-logged recovery model.
2. Make sure you that replica in Sync otherwise the backup of transaction log would not truncate the log.

Coming back to the question: Yes, log-intensive transactions like modifying a large amount of data in tables or even rebuild of the index can cause the unusual log growth. This is because of the fact that the log cannot be truncated to redo has completed the changes in all secondary replicas of the availability group. The transactions like CREATE INDEX or ALTER INDEX are log intensive by nature. You cannot eliminate the log generation, but you can make intelligent maintenance to reduce the impact of the index rebuild on production activities.

In general, you can, you following query which will show you what is preventing the log space reuse:

SELECT log_reuse_wait_desc,*
FROM sys.databases

Working with AlwaysOn Availability Groups can sometimes be very tough and tricky. Understand the basics and knowing the behavior can greatly improve our reasoning to why it behaves like this. Have you also encountered such situation ever in your environment? What did you do for that? Would love to hear from you on this for sure.

Reference: Pinal Dave (https://blog.sqlauthority.com)