AG issue

Question

Post reply

AG issue

ramyours2003

SSChampion

Points: 12268
More actions
August 1, 2022 at 12:27 pm

#4070861

Few databases are not in sync in an AG as we did not find any errors on secondary replica . any query to check the last log backup on secondary server and databases are in huge size (1TB). How to resync database with primary with out dropping and adding database in primary by restoring full back and log backups from primary .

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

sqlguru SSC-Addicted Points: 471 More actions · Answer 1

sqlguru

SSC-Addicted

Points: 471

August 1, 2022 at 2:45 pm

#4070943

Did you try Automatic seeding?

ramyours2003 SSChampion Points: 12268 More actions · Answer 2

No I don't want to remove and re add the database on AG in primary. I need a query to check the last log backup restored on secondary replica as and the process of synching both the replicas.

sqlguru SSC-Addicted Points: 471 More actions · Answer 3

I am not sure how you can do that in AG. Have you tried with data suspend and resume? Are you getting any errors?

This reply was modified 2 years, 4 months ago by sqlguru.

ramyours2003 SSChampion Points: 12268 More actions · Answer 4

no i did not tried with that option . i am assuming to take backup from primary and restore with no recovery and add the db back to AG . instead of that is any approach is there to sync the data again on both replicas .

CC-597066 SSCommitted Points: 1637 More actions · Answer 5

This is one of the commands I use to keep an eye on my AGs. I have never needed to use it but it pulls out the LSN info so you could probably use that to work out which log backup files need to be restored:

SELECT ar.replica_server_name,
       adc.database_name,
       ag.name AS ag_name,
       drs.is_local,
       CASE
           WHEN hags.primary_replica = ar.replica_server_name THEN
               1
           ELSE
               0
       END AS is_primary_replica,
       drs.synchronization_state_desc,
   ar.availability_mode_desc,
       drs.is_commit_participant,
       drs.synchronization_health_desc,
   drs.redo_queue_size / drs.redo_rate / 60.0 AS est_redo_completion_time_min,
       drs.log_send_queue_size,
       drs.log_send_rate,
       drs.redo_queue_size,
       drs.redo_rate,
       drs.last_sent_lsn,
       drs.last_sent_time,
       drs.last_received_lsn,
       drs.last_received_time,
       drs.recovery_lsn,
       drs.truncation_lsn,
       drs.last_hardened_lsn,
       drs.last_hardened_time,
       drs.last_redone_lsn,
       drs.last_redone_time,
       drs.filestream_send_rate,
       drs.end_of_log_lsn,
       drs.last_commit_lsn,
       drs.last_commit_time,
       hars.last_connect_error_number,
       hars.last_connect_error_description,
       hars.last_connect_error_timestamp
--select * 
FROM sys.dm_hadr_database_replica_states AS drs --hdrs
    INNER JOIN sys.availability_databases_cluster AS adc
        ON drs.group_id = adc.group_id
           AND drs.group_database_id = adc.group_database_id
    INNER JOIN sys.availability_groups AS ag
        ON ag.group_id = drs.group_id
    INNER JOIN sys.availability_replicas AS ar
        ON drs.group_id = ar.group_id
           AND drs.replica_id = ar.replica_id
    INNER JOIN sys.dm_hadr_availability_group_states AS hags
        ON hags.group_id = ag.group_id
    LEFT OUTER JOIN [master].[sys].[dm_hadr_availability_replica_states] hars
        ON (
               hars.group_id = drs.group_id
               AND hars.replica_id = drs.replica_id
           )
ORDER BY ag.name,
         ar.replica_server_name,
         adc.database_name;