0

We are currently running into this issue for one of our availability group design:-

On Primary :- Nightly Table switch/Purges are happening (DDL) for approx. 2-3 hours

On Secondary which is configured as readable replica there will be SELECT queries running every now and then , as we do not have a window defined for secondary, REDO thread on secondary tends to get blocked by those SELECT queries which keep coming.

Currently to mitigate this issue, manually kill all select queries which keep blocking REDO thread ( this is in turn block other readers) until we see last commit time or redone time from SSMS AG dashboard sync up with the time DDL operation is completed.

Database in question is RCSI

Questions:-

  1. Is this expected way to keep killing SELECT if you don't have a defined maint'ce window for secondary server

  2. How are you guys dealing with this situation? I mean is there a way or script available online that it is automated to KILL such transactions on secondary in FLY when DDL is running on primary and wait until committed on secondary "?

0
  1. disable read on secondary:

    USE [master] GO LTER AVAILABILITY GROUP [AG-Something] MODIFY REPLICA ON N'SQLSERVER-SECONDARY' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)) GO

  2. do your ddll workload on primary

  3. re enable read on secondary

    USE [master] GO

    ALTER AVAILABILITY GROUP [AG-Something] MODIFY REPLICA ON N'SQLSERVER-SECONDARY' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY))

    GO

you can chek the AG status between the step 2 and 3 with this query:

SELECT 
ar.replica_server_name, 
adc.database_name, 
ag.name AS ag_name, 
case HDRS.is_primary_replica
when 1 then 'Primary Replica'
else 'Secondary Replica'
end as Replica,
HDRS.synchronization_state_desc, 
HDRS.synchronization_health_desc, 
HDRS.recovery_lsn, 
HDRS.truncation_lsn, 
HDRS.last_sent_lsn, 
HDRS.last_sent_time, 
HDRS.last_received_lsn, 
HDRS.last_received_time, 
HDRS.last_hardened_lsn, 
HDRS.last_hardened_time, 
HDRS.last_redone_lsn, 
HDRS.last_redone_time, 
HDRS.log_send_queue_size, 
HDRS.log_send_rate, 
HDRS.redo_queue_size, 
HDRS.redo_rate, 
HDRS.filestream_send_rate, 
HDRS.end_of_log_lsn, 
HDRS.last_commit_lsn, 
HDRS.last_commit_time
FROM sys.dm_hadr_database_replica_states AS HDRS
INNER JOIN sys.availability_databases_cluster AS adc 
ON HDRS.group_id = adc.group_id AND 
HDRS.group_database_id = adc.group_database_id
INNER JOIN sys.availability_groups AS ag
ON ag.group_id = HDRS.group_id
INNER JOIN sys.availability_replicas AS ar 
ON HDRS.group_id = ar.group_id AND 
HDRS.replica_id = ar.replica_id
4
  • After step 2 how you are going to make sure if DDL work is committed or not before enabling read again? – Newbie-DBA Apr 9 at 20:58
  • maybe you can check it with this dmv: select db_name(database_id) as [Database],is_primary_replica, synchronization_state_desc,database_state_desc,is_suspended,suspend_reason_desc, recovery_lsn,truncation_lsn,last_sent_lsn,last_sent_time,last_received_lsn,last_received_time,last_hardened_lsn,log_send_queue_size,log_send_rate,redo_queue_size, redo_rate,end_of_log_lsn,last_commit_lsn,last_commit_time,secondary_lag_seconds from sys.dm_hadr_database_replica_states – MBuschi Apr 10 at 9:11
  • with secondary_lag_seconds you can check if the secondary is still having work to do – MBuschi Apr 10 at 9:12
  • @Newbie-DBA this might help: dba.stackexchange.com/questions/278324/… – SqlWorldWide Apr 11 at 21:23

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.