0

This question already has an answer here:

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.

marked as duplicate by Mark Storey-Smith, RLF, RolandoMySQLDBA, Max Vernon, billinkc Nov 10 '14 at 22:39

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • Seems like both logs are full, maybe because they are on the same disk. Investigate both causes that the message gives you. – usr Nov 10 '14 at 9:35
  • 1
    Look at the Disk Usage report -- it has a section that lists all the data and log file autogrowth events. Try to correlate the times with activity happening on the server. I suspect there's a long-running user process happening all in the same transaction. – Jon Seigel Nov 10 '14 at 14:50
  • Look into the default trace as well -- simple-talk.com/sql/performance/… to correlate the times as @JonSeigel suggested. Plus, Mike Walsh had a good answer -- dba.stackexchange.com/questions/29829/… – DenisT Nov 10 '14 at 14:58
0

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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.