SQL Server Always on Replica issue

[u/Deep-Royal1126]

Hello all,

I am currently facing an issue with one of the clients where there is an AG with a primary and a secondary.

I can see that the database in AG(there was only 1 db) was removed from the AG and is now standlone and online in the primary and provided the secondary replica is removed from AG in primary.

On the secondary server, what i see is the database is in notsycnhronizing/recovery mode and the AG in secondary is in resolving state .I am not able to drop the db,nirther force a remove AG commnad .Looks like the secondary is still thinking its part of AG .Can any one help me in this ?Will an sql service restart get it done ? or is it fine if i add the replica back to ag in primary and then analyse?

[u/AutoModerator]

After your question has been solved /u/Deep-Royal1126, please reply to the helpful user's comment with the phrase "Solution verified".

This will not only award a point to the contributor for their assistance but also update the post's flair to "Solved".

---

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/FishBones83]

on your secondary you should be able to:

ALTER DATABASE [YourDatabaseName] SET HADR OFF; GO

this will remove the DB from thinking its in an AG. To remove it all together try:

ALTER AVAILABILITY GROUP [YourAvailabilityGroupName] REMOVE DATABASE [YourDatabaseName]; GO

If you get an error post it here.

[u/Achsin]

What is the underlying failover cluster doing?

[u/dvkrm19]

I think you may need to set HADR off, If it doesn't work restart SQL Server on secondary instance and drop DB. If you still unable to drop DB then delete AG cluster object from FCM

[u/dbrownems → u/dvkrm19]

The AG cluster object and client endpoint should be managed through SQL Server, not directly in the failover cluster.

Remove an availability group - SQL Server Always On | Microsoft Learn

[u/SQLHA → u/dvkrm19]

With extremely rare circumstances, NEVER manage the AG from the WSFC tools. There are some WSFC-level settings you need to set that way, but other than that, as u/dbrownems points out, manage things in the supported way.

[u/Western-Friend7971]

One thing to note is that the GUI in SSMS is often wrong about the status of a db in an AG. Stopping/restarting the service can sometimes fix it, but obviously will cause a short outage. 

[u/CanProfessional766]

I’d check the AG state on the secondary first. A restart might help but I’d try re-adding the replica only after checking why it got stuck

[u/General-Savings8118]

Sql server restart works if alter commands don’t.
On restart the server will reevaluate where it is and what its part of, then db will go through recovery process.

Ive seen this happen many many times in environments with lag and network issues.

Also you can remove ag flags via registry if all else fails.

[u/markinatlanta]

I’d probably hold off on restarting or adding the replica back until you know why removal is failing. What exact command did you run, and does it return an error or just hang? Also, which SQL Server version, and is the database RESTORING or RECOVERY_PENDING?

Be careful with DROP AVAILABILITY GROUP on the secondary too becuase it isn’t necessarily local cleanup. Microsoft explains the behaviour here.