Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Removing mirroring session on a partner database in Mirror, Disconnected / In Recovery state Expand / Collapse
Author
Message
Posted Monday, June 6, 2011 4:07 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, June 17, 2011 10:05 AM
Points: 85, Visits: 394
I am trying to remove mirroring on a partner server database due to a network issue. Here is the scenario.

We are using synchronous mirroring with a Principal, Witness and Mirror server. Our databases are set up with .mdf files on the D: drive and the .ldf files on the E: drive on both Principal and Mirror.

A large log file growth on the Mirror server caused the E: drive to fill up and actually drop off the network. When the mirror log files disappeared, mirroring automatically paused for all databases on the Principal server. The network people were able to bring the E: drive back online. We removed mirroring completely from the Principal server; however, the Mirroring server now shows 3 of the 4 databases in the following state:

Mirror, Disconnected / In Recovery

The fourth database on the Mirror server is in the following state:

Recovering...

I can't work at all with the databases that are disconnected. We want to reconfigure mirroring from scratch. We tried to delete them and restore fresh databases from production .bak and .trn files, but receive an error saying "the database is enabled for database mirroring. Database mirroring must be removed before you drop the database." Also an error is generated when trying to use the ALTER DATABASE <database name> SET PARTNER OFF command on the Mirror database, saying "The command failed because the database mirror is busy. Reissue the command later."

It seems that removing mirroring from the Principal has not removed mirroring from the Mirror, but the databases are inaccessible.

I hope I've explained this properly. I believe I can still work with the fourth database, which is currently in a "Recovering..." state.

Any advice would be greatly appreciated.

Post #1120657
Posted Thursday, September 6, 2012 6:46 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 5:39 PM
Points: 286, Visits: 582
Old post revival!! Just wondered how you managed to get this one resolved as I've a similar situation.

Thanks


Clare
_________________________________________________________________________________________________________________
Measure twice; cut once (and have a good saw)

Hey, just a thought.....did you check Books Online yet?
Post #1355678
Posted Friday, September 7, 2012 1:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:07 AM
Points: 6,763, Visits: 14,427
MissTippsInOz (9/6/2012)
Old post revival!! Just wondered how you managed to get this one resolved as I've a similar situation.

Thanks

post full details into a new thread for more help


-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1355804
Posted Friday, September 7, 2012 2:26 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 5:39 PM
Points: 286, Visits: 582
Perry Whittle (9/7/2012)
MissTippsInOz (9/6/2012)
Old post revival!! Just wondered how you managed to get this one resolved as I've a similar situation.

Thanks

post full details into a new thread for more help


Thanks but its ok. I discovered the only ingredient required in my case was about 4 hours of patience


Clare
_________________________________________________________________________________________________________________
Measure twice; cut once (and have a good saw)

Hey, just a thought.....did you check Books Online yet?
Post #1355815
Posted Friday, September 7, 2012 2:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:07 AM
Points: 6,763, Visits: 14,427
Post it anyway with your solution as it may help others in the same situation

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1355835
Posted Friday, September 7, 2012 8:00 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 2:09 AM
Points: 1,606, Visits: 2,671
... Exactly... I too had same problem sometime back. I was totally lost.
At last I tried to detach the DB. It threw error saying "Cant detach". But when I refreshed the DB was actually detached. I attached again and some how the issue got resolved. If you have proper solution for this issue, please post it.

Thanks,
Smith.
Post #1355988
Posted Friday, September 7, 2012 11:51 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 28, 2012 6:58 AM
Points: 11, Visits: 51
alter database <mirrorDB> set partner FORCE_SERVICE_ALLOW_DATA_LOSS

This allows the mirror DB to come online without any further attempt on the server's part to apply more log bytes from the principal.
Post #1356151
Posted Wednesday, September 12, 2012 5:32 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 5:39 PM
Points: 286, Visits: 582
2ndHelping (9/7/2012)
alter database <mirrorDB> set partner FORCE_SERVICE_ALLOW_DATA_LOSS

This allows the mirror DB to come online without any further attempt on the server's part to apply more log bytes from the principal.


Thanks 2ndHelping. Its a nice idea but, unfortunately, not one that will work in this case.

As promised I will post up the scenario. I have mirroring set up in high-performance mode.

During a heavy processing load mirroring generates an error on the mirror server:
"the remote mirroring partner for database 'ABRS_NABNSA', encountered error 9003, status 15, severity 20. Database mirroring has been suspended"
The log on the mirror has "The log scan number (16524:1595833:2) passed to log scan in database '' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.
Attempting to resume the mirroring seems to have little effect, no errors are generated and the Database Mirroring Monitor shows 'Synchronizing' but with an impossibly slow restore rate.
Given that, in all of this the transaction log had blown out because the processes on the principal were still running I decided the most sensible thing to do was to remove the mirror, shrink the transaction log and reinitialise.
I ran ALTER DATABASE <DBName> SET PARTNER OFF on the principal, but when I went to run this command on the mirror I received the message "The command failed because the database mirror is busy. Reissue the command later.".
Attempting to run a RESTORE DATABASE WITH RECOVERY command returns a message " exclusive access could not be obtained because the database is in use"
At this stage the mirror database is at "Mirror, Disconnected/In Recovery".
I restarted the SQL Server service on the mirror server and received the message "Database mirroring has been terminated for database"
So I figured I'd just wait it out and see what happened.
Some 3 hours, the mirror database had recovered and was online.

So I concluded that there was some mammoth roll forward/back process that was going on in order to bring the (previously) mirrored database into a consistent state? Also I was unable to fathom why the "invalid logscan number" error was generated.

However, I just discovered a cumulative update http://support.microsoft.com/kb/2403218/en-us?sd=rss&spid=2855 which looks rather like the ticket!


Clare
_________________________________________________________________________________________________________________
Measure twice; cut once (and have a good saw)

Hey, just a thought.....did you check Books Online yet?
Post #1358268
Posted Monday, November 12, 2012 12:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 16, 2013 12:09 AM
Points: 1, Visits: 51
Hi there, I had the same situation, where I could not delete/drop the databases.
Here is my solution:
1. Alter database <Databasename> set partner FORCE_SERVICE_ALLOW_DATA_LOSS

Once you executed this command, you can check the status of the database by refreshing MSSQL Server Management Studio.
You can see that it now changed to (In Recovery)

2. Use MSSQL Server Management Studio, manually change the database properties.
. Right click on the database and go to its properties
. Go to Mirroring, then Click on Remove Mirroring
. Click OK
3. Refresh MSSQL Server Management Studio, then right click on the database to delete it.

Good luck, I hope that helps.
Post #1383534
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse