Additional SQL Server features and topics not covered by specific categories
Hi YashwantVishwakarma-9317,
Sorry for delay.
You can check the following things to troubleshoot this issue:
- Check if there are any long transactions on the primary replica. This query may help you: USE MASTER
GO
SELECT spid,
PROGRAM_NAME,
nt_userName,
loginame,
DB_NAME(s.dbid) AS DatabaseName,
CR.TEXT AS Query
FROM sysprocesses s
CROSS apply sys.Dm_exec_sql_text(sql_handle) CR
WHERE open_tran = 1 - Please check the SQL Server:Database Replica > Redo Bytes Remaining Performance Monitor counter on the secondary replicas. If this counter continues to increase, it means that the secondary replica could not process the log records fast enough for REDO, causing the primary replica to wait and for SQL Server to not be able to truncate the log on the primary replica.
- You can use the AlwaysOn Dashboard and sys.dm_hadr_database_replica_states dynamic management views to help monitor the log send queue and redo queue. Some key fields are:
- Please refer to High network latency or low network throughput and Disk I/O bottleneck slows down log hardening on the secondary replica.
Best Regards,
Amelia