Sunday, March 25, 2012

Database Mirroring Problems - Can't drop database

Doing some mirroring testing and have run into problems.
Firstly, the database failed over to the secondary server. This is fine
and I wanted to manually fail back to the Primary but the databases
would not synchronize. The database is < 4mb and I let it try to
synchronize for an hour, it had clearly got itself into a mess. I then
deleted the "restoring database" on the Primary server and restored
from backup, intending to setup mirroring again. The restore was fine.
However now on the secondary server the database thinks it is in the
"principal disconnected" state.
If I try and delete it, I cannot drop the database because it is
currently in use. (No user connections are active.)
"Alter database X set partner off" fails as it says it cannot connect
to the mirror.
Any "alter database" variation (like setting it to single user), will
fail as SQL thinks the database "is involved in a database mirroring
session".
It fails if I try and detach it with - 'only user processes can be
killed.
I can't think of anything else to try. So how can I drop this database
and get it going again?
TIA,
S.
Hi
Have you tried issuing ALTER DATABASE <database_name> SET PARTNER OFF?
John
"Calculated" wrote:

> Doing some mirroring testing and have run into problems.
> Firstly, the database failed over to the secondary server. This is fine
> and I wanted to manually fail back to the Primary but the databases
> would not synchronize. The database is < 4mb and I let it try to
> synchronize for an hour, it had clearly got itself into a mess. I then
> deleted the "restoring database" on the Primary server and restored
> from backup, intending to setup mirroring again. The restore was fine.
> However now on the secondary server the database thinks it is in the
> "principal disconnected" state.
> If I try and delete it, I cannot drop the database because it is
> currently in use. (No user connections are active.)
> "Alter database X set partner off" fails as it says it cannot connect
> to the mirror.
> Any "alter database" variation (like setting it to single user), will
> fail as SQL thinks the database "is involved in a database mirroring
> session".
> It fails if I try and detach it with - 'only user processes can be
> killed.
> I can't think of anything else to try. So how can I drop this database
> and get it going again?
> TIA,
> S.
>
|||John Bell wrote:
> Hi
> Have you tried issuing ALTER DATABASE <database_name> SET PARTNER OFF?
> John
>
Thanks for the reply John. Yes I have. The command returns:
Msg 1404, Level 16, State 11, Line 3
The command failed because the database mirror is busy. Reissue the
command later.
Which is not helpful as the "mirror" in this case is a new database
sans any mirroring. This error also occurs if I attempt to pause the
mirroring in the Management Studio.
|||Have now been able to delete the database - restarting the SQL service
on the secondary database put the database into a state where it was
able to be deleted. I didn't really want to do this but it has worked
ok and the database is no more.

No comments:

Post a Comment