patternMinor
Zombie Sql Server DB on Secondary Replica is both in and out of an Availability Group
Viewed 0 times
groupsqlreplicaandbothsecondaryzombieavailabilityserverout
Problem
I have a database in SQL Server 2012 that is on the secondary replica of an availability group. I removed the database from the AG db list on the primary and the db is currently in the Restoring state on the secondary.
The problem is that I'm unable to drop the database off the secondary.
Gives me the following error:
"The database 'ZOMBIEDB' is currently joined to an availability group.
Before you can drop the database, you need to remove it from the
availatility group."
However:
Yields the following error:
"Cannot complete this ALTER DATABASE SET HADR
operation on database 'ZOMBIEDB'. The database is not joined to an
availability group. After the database has joined the availability
group, retry the command."
So, how do I convince SQL Server that the db isn't in an AG so I can drop it?
The problem is that I'm unable to drop the database off the secondary.
DROP DATABASE [ZOMBIEDB]Gives me the following error:
"The database 'ZOMBIEDB' is currently joined to an availability group.
Before you can drop the database, you need to remove it from the
availatility group."
However:
ALTER DATABASE [ZOMBIEDB] SET HADR OFF;Yields the following error:
"Cannot complete this ALTER DATABASE SET HADR
operation on database 'ZOMBIEDB'. The database is not joined to an
availability group. After the database has joined the availability
group, retry the command."
So, how do I convince SQL Server that the db isn't in an AG so I can drop it?
Solution
I pulled the oldest full backup I could find and was able to restore that over the Restoring db using:
I was then able to delete the restored database.
RESTORE DATABASE [ZOMBIEDB] FROM DISK = N'D:\ZOMBIEDB.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 5I was then able to delete the restored database.
Code Snippets
RESTORE DATABASE [ZOMBIEDB] FROM DISK = N'D:\ZOMBIEDB.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 5Context
StackExchange Database Administrators Q#60442, answer score: 2
Revisions (0)
No revisions yet.