SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Removing mirroring session on a partner database in Mirror, Disconnected / In Recovery state


Removing mirroring session on a partner database in Mirror, Disconnected / In Recovery state

Author
Message
kwilt
kwilt
Mr or Mrs. 500
Mr or Mrs. 500 (573 reputation)Mr or Mrs. 500 (573 reputation)Mr or Mrs. 500 (573 reputation)Mr or Mrs. 500 (573 reputation)Mr or Mrs. 500 (573 reputation)Mr or Mrs. 500 (573 reputation)Mr or Mrs. 500 (573 reputation)Mr or Mrs. 500 (573 reputation)

Group: General Forum Members
Points: 573 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.
MissTippsInOz
MissTippsInOz
Right there with Babe
Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)

Group: General Forum Members
Points: 793 Visits: 597
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?
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)

Group: General Forum Members
Points: 53689 Visits: 17672
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" ;-)
MissTippsInOz
MissTippsInOz
Right there with Babe
Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)

Group: General Forum Members
Points: 793 Visits: 597
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?
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)

Group: General Forum Members
Points: 53689 Visits: 17672
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" ;-)
Joy Smith San
Joy Smith San
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6346 Visits: 3200
... 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.
2ndHelping
2ndHelping
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 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.
MissTippsInOz
MissTippsInOz
Right there with Babe
Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)

Group: General Forum Members
Points: 793 Visits: 597
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?
hnguye42
hnguye42
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 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.
Vijay Mane DBA
Vijay Mane DBA
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 2
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search