question

sakuraime avatar image
0 Votes"
sakuraime asked Criszhan-msft commented

How to check AAG replica sync status

Suppose I am going to shutdown a 2nd replica for certain maintenance (like down for serveral hours) . And the up the 2nd replica again , and it should join back , and the database should be sync and catchup the log


I would like to monitor this process, like how much time still need to catch up , so that the database in the 2nd replica be comming synchroinzed again .

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Criszhan-msft avatar image
0 Votes"
Criszhan-msft answered Criszhan-msft commented

Hi,

AG dashboard or related DMV may be helpful.
https://sqlperformance.com/2015/08/monitoring/availability-group-replica-sync

Note that the secondary replica is not online for a long time may cause the transaction log of the primary database to continue to grow.
As long as a secondary database remains suspended, the transaction log of the corresponding primary database cannot be truncated. This causes log records to accumulate on the primary database. Therefore, we recommend that you resume, or remove, a suspended secondary database quickly.
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/suspend-an-availability-database-sql-server?view=sql-server-ver15

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

may I know which column from the dashboard show that how much time remaining for catchup after join back ?

0 Votes 0 ·

Estimated Recovery Time (seconds)
Indicates the time in seconds it takes to redo the catch-up time. The catch-up time is the time it will take for the secondary replica to catch up with the primary replica. This value is hidden by default.

Mode details please refer to the MS doc.
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/use-the-always-on-dashboard-sql-server-management-studio?view=sql-server-ver15#group-by-availability-group-information




0 Votes 0 ·

I have tested, but Estimated Recovery Time always show 1 and 0 seconds ,

even it's still synchronizing ( I mean the database status )

0 Votes 0 ·
Show more comments