December 16, 2014 at 10:22 am
So I have a database that did not switch role correctly.
Ultimately the decision was made to 're-mirror'.
On the principal I 'unmirrored' the database.
Backup up db & log.
Copied over to mirror server.
Went to remove the mirror (in disconnected, mirror state) & it won't allow me to drop it because it's involved in mirroring.
I tried to bring online & also ran the following:
ALTER DATABASE [] SET PARTNER OFF
RESTORE DATABASE [] WITH RECOVERY
In all cases I get "the command failed because the database mirror is busy".
This forum post (http://www.sqlservercentral.com/Forums/Topic1120657-146-1.aspx) suggests I run:
ALTER DATABASE [] SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
But I'm nervous. Could this have any effect on my live, (original principal, now standalone) copy of the database.
In the mirroring dmvs I can see that the (original principal) is not a mirror but the (original mirror) still thinks it's a mirror copy & has details of the (original principal). I don't care what happens to the disconnected mirror as I will overwrite it, but if it affects the live database I'm toast.
No witness.
There was, but it was removed by someone else just to make things even more messy & complicated at this stage.
Appreciate your time!
Thanks.
December 17, 2014 at 2:13 am
Mmmm, I'm 98.86% the force allow option is the correct next step ...
Failing that a SQL restart.
Any 2nd opinons?
Thank you.
December 17, 2014 at 4:23 am
i seem to remember this was an issue in 2008.
The quick fix is to stop the sql server services on the mirror and remove the database files (or rename them) for the problem database. When you restart the services the database entry may then be deleted\dropped.
Since there is no longer a mirror relationship it will not affect your Primary
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 17, 2014 at 8:14 am
Thanks Perry,
I DID need to restart SQL as you suggested.
Just a restart was sufficient in this case. I didn't need to touch the data files (which was plan B). The - now orphaned - mirror came up & I was able to drop it in the usual way.
Appreciate your time.
🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply