reason:
The log has reached the maximum space on the primary copy or the disk is full.
analysis
The log block of the primary replica can only be reused after it is fixed and redo on other replicas.
So if
1. Transmission delay, due to network delay or bandwidth delay.
2. Copy redo is slow due to delay, blocking or insufficient resources.
Causes the log to grow and cannot be backed up.
log_ send_ queue_ Size: a log block that has not been received by the replica. More than one log block means delivery delay.
redo_ queue_ Size: there is no redo log block on the replica. If there is more, it means redo delay.
SELECT ag.name AS [availability_group_name]
, d.name AS [database_name]
, ar.replica_server_name AS [replica_instance_name]
, drs.truncation_lsn , drs.log_send_queue_size
, drs.redo_queue_size
FROM sys.availability_groups ag
INNER JOIN sys.availability_replicas ar
ON ar.group_id = ag.group_id
INNER JOIN sys.dm_hadr_database_replica_states drs
ON drs.replica_id = ar.replica_id
INNER JOIN sys.databases d
ON d.database_id = drs.database_id
WHERE drs.is_local=0
ORDER BY ag.name ASC, d.name ASC, drs.truncation_lsn ASC, ar.replica_server_name ASC
resolvent:
1. Remove the DB from the most delayed replica and join it later.
2. If the redo thread on the replica is blocked by frequent read operations, set the replica as unreadable and change it back later.
3. If there is still space on the disk, the log file will grow automatically.
4. If the maximum space limit is reached and the disk still has space, increase the maximum space limit.
5. If the log file reaches the maximum value of 2T system and there are idle disks, add the log file.
reference material
https://docs.microsoft.com/en-US/troubleshoot/sql/availability-groups/error-9002-transaction-log-large
