June 6, 2011 at 4:07 pm
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.
September 6, 2012 at 6:46 pm
Old post revival!! Just wondered how you managed to get this one resolved as I've a similar situation.
Thanks
September 7, 2012 at 1:58 am
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" 😉
September 7, 2012 at 2:27 am
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:-)
September 7, 2012 at 2:57 am
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" 😉
September 7, 2012 at 8:00 am
... 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.
September 7, 2012 at 11:51 am
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.
September 12, 2012 at 5:32 pm
2ndHelping (9/7/2012)
alter database <mirrorDB> set partner FORCE_SERVICE_ALLOW_DATA_LOSSThis 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!
November 12, 2012 at 12:59 am
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.
January 23, 2015 at 1:38 pm
Case:
When u run below command on Principal server:
alter database 'Database_Name' set partner off
After running above command your DB goes in [mirror, disconnected] State on mirror server.
Resolution:
Step 1:Run below query on mirror
exec sp_resetstatus 'Database_Name'
Step 2: After running below command your DB will be available.
restore database 'Database_Name' with recovery
Note: Step 2 will take some time to recover.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply