4

I have a database with a log that is constantly growing. It is in full recovery mode, and has a log backup every 15 minutes. It is also part of an availability group, and has a readable secondary. It is currently over 22GB in size, where the data is only 15GB. I have a certain amount of time to look into this, because the drive for logs is 60GB. sys.databases.log_reuse_wait_desc is AVAILABILITY_REPLICA for this db.

I started by trying to find out why I couldn't shrink the log, and the answer seems to be because of the readable secondary in the availability group. However, "don't shrink the log - stop it growing" seemed to be a theme in the answers I looked at. So I looked for some information on that, and found this and this.

I plan to talk to our development team, and discuss their code causing the issues, but I don't want to approach them without some additional information. What can I do to find the queries that are likely to be the culprits for growing the logs?

3

I found a number of troubleshooting tips for this situation here.

I've summarized them below:

"AVAILABILITY_REPLICA" indicates that the logged changes in the availability database at the primary replica that have not arrived and that these changes were applied to the availability database at one of the secondary replicas. Until logged changes arrive and are applied, the changes cannot be truncated from the availability database log at the primary replica.

Troubleshooting

"Log Send Queue" and "Redo Queue" are measurable data points during availability database synchronization. You can monitor these data points in order to determine whether an availability database log cannot be truncated because of the log uses type AVAILABILITY_REPLICA.

  • Log Send Queue - When a transaction is performed at the primary, the logged blocks must be delivered and hardened to the database log file at the secondary. Any delay will prevent truncation of those logged changes in the database at the primary replica. A common reasons for a sustained log send queue is latency in the network or during hardening (write to disk) of the log blocks on the secondary.
  • Redo Queue - As soon as it is hardened to the secondary database log file, a dedicated redo thread applies the log records. If the redo operation cannot keep up with the transaction log that is generated, log growth may occur. If the secondary replica redo operation is behind in applying those changes to a corresponding secondary database, the primary will truncate the transaction log.

Identify the secondary database that is delaying log truncation

If there is more than one secondary, there may be a particular secondary that is responsible for the log truncation issue. To identify the secondary database that is delaying log truncation, log on to the primary replica, query the truncation_lsn column of the sys.dm_hadr_database_replica_states dynamic management view (DMV), and then review the log_send_queue and redo_queue data points to help diagnose the issue.

The AlwaysOn dashboard and the sys.dm_hadr_database_replica_states DMV help monitor the redo progress. The following table lists some key fields:

enter image description here

For example, execute the following query against the primary replica in order to report the log_send_queue and redo_queue values for each secondary availability database.

SELECT ar.replica_server_name
    ,drs.truncation_lsn
    ,drs.log_send_queue_size
    ,drs.redo_queue_size
FROM sys.dm_hadr_database_replica_states drs
JOIN sys.availability_replicas ar ON drs.replica_id = ar.replica_id
WHERE drs.is_local = 0

Corrective measures may include but are not limited to the following:

  • Make sure that there is no resource or performance bottleneck of the secondary.
  • Make sure that the Redo thread is not blocked in the secondary.
  • Make sure that the Redo thread does not fall behind because resource contention created a reporting workload.
  • I poked a little deeper, and found it was caused by my secondary node being down. Restarting it was a simple as going to the failover cluster manager, and choosing start cluster service. Now it's synchronizing... – BeanFrog Mar 3 '17 at 15:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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