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
Read More:
- Oracle can’t start the database due to deleting DBF file by mistake
- Log search of SQL execution on distributed green plus database master
- Deploy mongodb fragment combined with replica set to realize distributed storage of MySQL database files (step 10)
- Error: current transaction is aborted, commands ignored until end of transaction blockp
- In the XML configuration file of spring jdbc.properties The connection to the database failed due to garbled code
- Error 945 Database cannot be opened due to inaccessible files or insufficient memory or disk space
- SQL Server 2005, unable to log in, forget sa password, 15405 error!
- How to remove the message “Membership credential verification failed.” displayed in the Event Log
- A series of errors encountered in connecting to the database using Oracle SQL developer
- After switching the tidb database, an error could not commit JDBC transaction appears from time to time
- [Java] spring transaction control configuration error, application- persstence.xml Transaction manager report red in file
- An error is reported when logging into mysql. The error message is error 1049 (42000): unknown database ‘XXXXXX’
- Asset database transaction committed tweet appears when unity reports an error!
- VirtualBox problem solving set -[drm:vmw_host_log [vmwgfx]] *ERROR* Failed to send host log message
- Pikachu vulnerability is installed in the shooting range, and an error is reported when connecting to the MySQL database
- SQL Server calls Database Mail to send mail error: Msg 229, Level 14, State 5, Procedure sp_send_dbmail, Line 1 EXECUTE
- In idea, @ slf4j is used to report an error, but log is not found
- An error occurred during win10 PostgreSQL installation: “failed to load SQL modules into the database cluster“
- An error is reported when kettle connects Oracle database and MySQL database
- Solution to the problem that SQL database query result field contains new line character, which leads to copy to excel dislocation