MSSQL cannot remove orphan/stuck data base from Availability Group

Question

Hello,

I have a cluster setup with availability group and a database was part of this AG, but something happened and I can see it in the AG, but with an exclamation mark.

I tried to alter the availability group and remove it, but the error was that the database is not part of the AG.

I added a picture with the AG.

Can someone tell me how I can remove that database from the AG and added again in the AG?

Thanks,

Daniel

Answer 1

Hi @Mitu Daniel Marius (IBM) ,

Thank you for your reaching out and welcome to Microsoft Q&A!

I tried to alter the availability group and remove it, but the error was that the database is not part of the AG.

We'd better verify whether the database joined the AG. If you refreshed and it was still not there, then this means that the database does not exist. The reason would/could be that something went wrong with automatic seeding.

You should try executing these commands on the secondary instance:

ALTER AVAILABILITY GROUP [agname] JOIN; 
GO 
ALTER AVAILABILITY GROUP [agname] GRANT CREATE ANY DATABASE; 
GO 

After this you should retry automatic seeding & validate that the database was created and fully seeded on the secondary instance.

You can also use sys.databases which has a column called group_database_id. If this column is null then the database is not participating in an availability group. To only get user databases, restrict the query to database_id > 4.

SELECT name From sys.databases WHERE database_id > 4 AND group_database_id IS NULL

Can someone tell me how I can remove that database from the AG and added again in the AG?

Please check out this article first, it shows the method how to resolve the error message you posted.

In addition to that article, you can try to pull the oldest full backup you can find to restore that over the Restoring db using:

RESTORE DATABASE [ZOMBIEDB] FROM  DISK = N' ' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 5

Then you can try to remove the database again.

If you want to remove the database directly, please try to disable all backup jobs in the primary.

Then, right click on the availability group name on primary server and remove the database from the availability group.

NOTE: Could you please provide the detailed log here to help us narrow down the issue?

Thanks for your understanding. Your time and cooperation are much valued by us. We are looking forward to hearing from you to assist further.

Best regards,

Lucy Chen

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

https://docs.microsoft.com/en-us/answers/support/email-notifications