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.
- a backup of the transaction log file is made each 5 minutes
AlwaysOn Availability Groupsis enabled
- the transaction log file size is 128 MB (only 1-5 percent of it are in used)
After several days:
- 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
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.