I'm having a bad issue with Always ON on a SQL Server 2017 CU3 running on Windows Server 2016 with last cumulative updates (March 2018). Availability Mode is set to Synchronous commit and when I reboot the secondary node some DBs (randomly) get in a "Not Synchronized" state and they can't go back to "Synchronized". Everything looks OK, I have another SQL installation with same patching level and Os and it's working fine. I don't have specific errors on the event viewer. Antivirus is disabled, the nodes are on the same network and they look ok. I tried to raise the session timeout to 20s but this didn't solve the issue. Do you have any idea or suggestion?

  • 1
    If this is repeatable (which seems to be the case), I recommend contacting Microsoft support about this. Based on my experience with AGs, there is still a lot of work needed to avoid conditions like this, detect them when they do occur, and to log helpful messages. – Tony Hinkle Mar 23 '18 at 14:38

I had the same issue on SQL Server 2017 CU 11 (latest as of today) with Windows Server 2016. After rebooting the secondary replica, some of the databases in the AG show not synchronizing. I used ALTER database SET HARD RESUME, but it didn't work. The work around is to restart the SQL Server Services on the secondary replica. Then all databases show synchronized again.

| improve this answer | |
  • Welcome on the DBA SE! This sentence: "The database not synchronizing are random" looks a little bit cloudy to me, it is probably a translation problem. Could you please edit your sentence into a more elaborated form? – peterh - Reinstate Monica Sep 28 '18 at 0:21
  • We're experiencing the same issue as you with SQL 2017 CU13 on WS2016. Happens every time we restart a node. Only solution so far is to restart the SQL Server Service to get the databases synchronized. – pauska Jan 7 '19 at 9:05

Look at your Extended Events at AlwaysOn_health. Maybe you can get more specific information there. I've never seen that randomly fail, so it's not a bad idea to open a case with msft.

| improve this answer | |

May I know how do you resolve the issue when seeing the DBs in the "Not Synchronizing" status? And you were saying only some random DBs in that state, while some other DBs are in a healthy state on the same instance?

I could only think of some possible causes:

  • The DBs that went into "Not Synchronizing" state is a heavy-load transactional database where when the secondary node rebooted, the redo logs couldn't catch up with the primary node. How about trying "Asynchronous" mode and reboot your secondary to see if the issue still present?
  • There's might also be some issues with the DNS name thingy. Did you check out the cluster event logs? This would probably need a Windows guy to help resolving it.
| improve this answer | |

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.