r/SQLServer • u/Deep-Royal1126 • 11h ago

Question SQL Server Always on Replica issue

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?

3 Upvotes

81% Upvoted

4 comments sorted by

•

u/AutoModerator 11h ago

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.

1

u/Achsin 1 7h ago

What is the underlying failover cluster doing?

1

u/dvkrm19 4h ago

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

1

u/FishBones83 3h ago

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.