AG Issues (one node is down) and all but 2 databases is accessible

• SQLSalas

  Ten Centuries

  Points: 1327

  More actions

  November 16, 2020 at 4:08 pm

  #3808345

  we have an AG of 4 nodes.  Node1 being the primary, (has about 27 databases in the AG).

  Node2 went down for some reason (VM is toast?)

  But being a 4 node AG, i would expect that the environment or the databases will still be accessible from the primary.   Unfortunately this is not the case.  All databases in that Primary node , i also not accepting connections, despite it being "ONLINE"

  "Unable to access availability database '<DBName>' because the database replica is not in the PRIMARY or SECONDARY role. Connections to an availability database is permitted only when the database replica is in the PRIMARY or SECONDARY role. Try the operation again later.

  Weird though, 2 out of the 27 is accessible?

  I tried removing the Node 2 out of AG, but it just runs for forever with this Wait Description:  HADR_AR_CRITICAL_SECTION_ENTRY

  Any one experienced this? And what would you advise?

   

• Site Owners

  SSC Guru

  Points: 80320

  More actions

  November 17, 2020 at 4:10 pm

  #3808761

  Thanks for posting your issue and hopefully someone will answer soon.

  This is an automated bump to increase visibility of your question.

• as_1234

  SSCrazy

  Points: 2842

  More actions

  November 18, 2020 at 11:18 am

  #3809146

  I don't know the cause of this but maybe the following will help work out the issse:

  Can you run the following?:

  select * from sys.dm_hadr_availability_replica_states

  This should show the status of each of the nodes and the availability group so might provide a clue as to what's causing this.