A transaction log grows unexpectedly

Question

I am trying to understand what process is making my transaction log file to grow. For example, currently it is 120 GB and only 5-10 MB of it are in use.

Before:

1. FULL recovery mode
2. a backup of the transaction log file is made each 5 minutes
3. AlwaysOn Availability Groups is enabled
4. the transaction log file size is 128 MB (only 1-5 percent of it are in used)

After several days:

1. The transaction log file size is 120 GB (again only few MB of it are in use)

I am trying to detect what is causing this huge grow. One of the possible reasons explained here is The database is participating in an AlwaysOn availability group.

I have try to find this particular error:

Error: 9002, Severity: 17, State: 9. The transaction log for database '%.*ls' is full due to 'AVAILABILITY_REPLICA'

But I have only errors for the transaction lot of the tempdb:

The transaction log for database 'tempdb' is full due to 'active_transaction'

Is there any message or error which will help me to understand when or why this is happening?

Is there any other way to detect the event?

As far as I read, I can only use DBCC OPENTRAN but it will give me current transaction which is opened which is useless for me as I do not know when the event happened.

Answer 1

You are just not catching it at the right times, there are lots of operations that will use the transaction log.

• When do you do maintenance if any (defragment indexes, CHECKDB, etc.)? These all use the transaction log.

• Do you have any jobs that load/modify data at night? These operations can use the transaction log as well.

As Jon Siegel suggested, use the disk usage report to get the times when it's filling up and correlate that with other activity.