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 ?
Thank you everyone !