Over the weekend a scenario occured where both of the Servers in a Mirrored environment were the Principal server and both were accessible. In the process of restarting services, the wrong server finally took over and became the real Principle, leaving the mirrored environment unable to synchronize. I have removed Mirroring from the principle and it is running in production stand alone. I still have the mirrored database with 9 hours of transactions that I need to recover. Is there anyway to restore that database even though it is in an unsynchronized state?
When I try to Remove Mirroring it gives me the following error: "Synchronization of the mirror database was interrupted, leaving the database in an inconsistent state. The ALTER DATABASE command failed. Ensure that the mirror database is back up and online, and then reconnect the mirror server instance and allow the mirror database to finish synchronizing." The problem with this error is the fact that synchronization will never occur.
Does anyone know anyway around this problem so that I can restore the Mirror?
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Thank you for your response. I have already tried your above suggestion and the following error is returned.
Msg 1404, Level 16, State 4, Line 1
The command failed because the database mirror is busy. Reissue the command later.
I just tried again (4 days since the mirror went down) and got the same message.
Has anyone ran into this problem before?
See if this helps: http://www.developersdex.c
It suggests you disable mirroring first: ALTER DATABASE YOUR_DB SET PARTNER OFF
Thanks again for your response. I did come across that article during my attempts at restoring the mirror and when I try to disable mirroring with the SET PARTNER OFF command on the Mirror I get the following error.
--------------------------
Msg 1457, Level 16, State 1, Line 1
Synchronization of the mirror database, 'dbName', was interrupted, leaving the database in an inconsistent state. The ALTER DATABASE command failed. Ensure that the mirror database is back up and online, and then reconnect the mirror server instance and allow the mirror database to finish synchronizing.
--------------------------
The mirror was technically "Ahead" of the Principle at the time I removed mirroring from the principle. I've really explored every option I know at this point and came to this site to see if there was an unpublished Stored Procedure that will clean it up and restore it to an accessible state.
We are currently in the process of moving to SQL CLustering, since Mirroring has been unstable for us over the last year. I find it hard to believe that Microsoft would allow for this scenario to occur and leave a database completely inaccessible, but it wouldn't surprise me.
If anyone has any magic tricks they would like to perform at this point, please let me know.
I really appreciate all of your suggestions. I have tried to issue an Alter Database Set Witness Off command, but it returns the "Mirror is busy" error. In the sys.database_mirroring table, the state of the witness is Unknown. I have no idea what else to try. I have a call into Microsoft currently and hope to talk to them tomorrow.
Ok. I'm stumped too. I'd appreciate it if you posted any response, as I was looking to implement a high safety with automatic failover mirror for a major client. My experience with Microsoft PSS is that you do get a good result quickly.
I'm also at a SQL conference this weekend and if the opportunity arises I'll find out what experiences other people are having.
Business Accounts
Answer for Membership
by: SteveH_UKPosted on 2008-02-26 at 23:13:00ID: 20991971
You need to use the FORCE SERVICE method. See http://technet.microsoft.c
Extract:
Connect to the mirror server.
Issue the following statement:
ALTER DATABASE <database_name> SET PARTNER
FORCE_SERVICE_ALLOW_DATA_L
where <database_name> is the mirrored database.
The mirror server immediately transitions to principal server, and mirroring is
suspended.
--------
You should remove the mirroring configuration after the database has been recovered.