I'm having a strange issue with a SQL server hosting 11 Availabilty Groups.

All those groups have 1 or 2 databases in it and they are synchronous commit with the automatic failover.

I noticed recently that one of the database had a state "Syncronizing" (which is not expected for synchronous commit). When I looked at the AG health state, it was "PARTIALLY_HEALTHY".

I looked at the extended event and did not see anything interesting.

I tried the "Resume" command to fix it but it didn't work. I ended up changing my AG configuration from synchrounous commmit to Asyn and set it back to sync which does resolve my issue (DB went back to synchronized).

One day later, I had a look at the same server, to make sure everything was still fine and found out that another database (in another AG) was showing "Synchronizing"...

So their must be an issue with my server... I look at my databases and at the last_hardened_lsn, they are not sync (primary vs secondary).

The server is sleeping, no activity at all right now...

I try the alter database x set hadr resume again and it doesn't change anything.

Is there a limitation to the number of AG on an instance ?

Any idea where I should look at to find the explanation for those strange issue ?

Here's what's in the dm_hadr_database_replica_states dmv: enter image description here enter image description here enter image description here

Thank you everyone !

  • In these cases, I usually will also check the cluster log and see whether there is anything suspicious there. With HAG (High Availability Group) setup, I find any "cold" or "cough" from the cluster will bring weird and hard-to-explain symptoms to the HAG.
    – jyao
    Nov 23, 2018 at 17:36
  • 1
    What's the output from sys.dm_hadr_database_replica_states at the time? Nov 27, 2018 at 2:46
  • Well, there is a lot of columns in this DMV, the syncronization_state_desc was "SYNCHRONIZING" and the synchronization_health_desc was "PARTIALLY_HEALTHY" Nov 29, 2018 at 18:21
  • 1
    check the sql server error log .. it will tell you why the state change happened. It will log it as informational message though.
    – Kin Shah
    Nov 30, 2018 at 17:34


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.