October 26, 2012 at 7:21 am
I have an interesting situation after playing around in a test installation: a mirrored DB that's locked in a mirrored state and can no longer be used.
Slowly..
I created a mirrored DB, added a new datafile to the principal using a path the mirror can't access. As a result the mirroring session was suspended because the mirror stated: "CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file".
Fine, so I disabled mirroring on the principal (SET PARTNER OFF) which worked just fine, but the mirror stayed in a mirrored state, Object Explorer saying:"(Mirror, Disconnected / In Recovery). Sooo, I tried to disable db mirroring on the mirror using ALTER DATABASE db SET PARTNER OFF;, which completed successfully, but the DB STILL remained in a mirrored-configuration.
I tried ALTER DATABASE db SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS which resulted in
"Msg 1404, Level 16, State 4, Line 1
The command failed because the database mirror is busy. Reissue the command later."
I tried taking the DB offline or restoring it from a backup, but all these operations resulted in "The operation cannot be performed on database "db" because it is involved in a database mirroring session or an availability group."
The only solution I can think of at the moment is shutting down the instance and deleting the data and log-files of the mirrored db, which would be just fine because this is just a test installation, but it would be not quite as easy in a productive environment.
Am I missing something? Is there any other way to remove the mirrored state from a disconnected mirror or to simply get rid of the db entirely to perform a recovery?
October 26, 2012 at 8:21 am
w8buch 88800 (10/26/2012)
Sooo, I tried to disable db mirroring on the mirror using ALTER DATABASE db SET PARTNER OFF;, which completed successfully, but the DB STILL remained in a mirrored-configuration.
It may have appeared to complete successfully, check the logs for more information as there has likely been a failure
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 26, 2012 at 8:36 am
Every time I try to issue the SET PARTNER OFF this gets written to the log:
"2012-10-26 07:29:21.63 spid36s Error: 5123, Severity: 16, State: 1.
2012-10-26 07:29:21.63 spid36s CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file 'M:\FailFile.ndf'.
2012-10-26 07:29:21.63 spid36s Error: 5123, Severity: 16, State: 1.
2012-10-26 07:29:21.63 spid36s CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file 'M:\FailFile.ndf'.
2012-10-26 07:29:21.63 spid36s Error: 1454, Severity: 16, State: 1.
2012-10-26 07:29:21.63 spid36s Database mirroring will be suspended. Server instance 'NODE02' encountered error 5123, state 1, severity 16 when it was acting as a mirroring partner for database 'IFail'. The database mirroring partners might try to recover automatically from the error and resume the mirroring session. For more information, view the error log for additional error messages.
"
That's the added file from the principal which cause the session to suspend in the first place, as there is no drive M: on the mirror.
And that's why I want to disable the mirroring, to correct this error, but first I have to correct the error to disable mirroring?
:w00t:
---EDIT---
By the way, after adding a drive M: to the mirror the SET PARTNER OFF query finally succeeded and the mirror is now in a restoring state, but that feels like cheating as this might not be practical/doable in a productive environment to simply add new drives to a box
October 26, 2012 at 8:45 am
so what is the current state of the principal is it still connected?
Post the results of the following please against the principal instance
select * from sys.database_mirroring
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 26, 2012 at 8:56 am
Nope, mirroring was removed from the principal right after mirroring was suspended. Resuming mirroring (before disabling it on the principal) was also impossible (without adding drive M to the mirror), as the mirror always tried to create the new file on the on existing drive.
October 26, 2012 at 9:08 am
Well, I recreated the whole situation (as I've solved the first situation by adding drive M) and this is the state after adding the new datafile, which cause the mirroring session to suspend:
Principal:
database_id mirroring_guid mirroring_state mirroring_state_desc mirroring_role mirroring_role_desc mirroring_role_sequence mirroring_safety_level mirroring_safety_level_desc mirroring_safety_sequence mirroring_partner_name mirroring_partner_instance mirroring_witness_name mirroring_witness_state mirroring_witness_state_desc mirroring_failover_lsn mirroring_connection_timeout mirroring_redo_queue mirroring_redo_queue_type mirroring_end_of_log_lsn mirroring_replication_lsn
----------- ------------------------------------ --------------- ------------------------------------------------------------ -------------- ------------------------------------------------------------ ----------------------- ---------------------- ------------------------------------------------------------ ------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------------------- ------------------------------------------------------------ --------------------------------------- ---------------------------- -------------------- ------------------------------------------------------------ --------------------------------------- ---------------------------------------
20 A9F43420-E956-4CF2-B669-A75340C63479 0 SUSPENDED 1 PRINCIPAL 1 2 FULL 1 TCP://node02.testing.home:5022 NODE02 TCP://node03.testing.home:5022 1 CONNECTED 33000000014400001 10 NULL UNLIMITED 33000000014400001 33000000014400001
Mirror:
database_id mirroring_guid mirroring_state mirroring_state_desc mirroring_role mirroring_role_desc mirroring_role_sequence mirroring_safety_level mirroring_safety_level_desc mirroring_safety_sequence mirroring_partner_name mirroring_partner_instance mirroring_witness_name mirroring_witness_state mirroring_witness_state_desc mirroring_failover_lsn mirroring_connection_timeout mirroring_redo_queue mirroring_redo_queue_type mirroring_end_of_log_lsn mirroring_replication_lsn
----------- ------------------------------------ --------------- ------------------------------------------------------------ -------------- ------------------------------------------------------------ ----------------------- ---------------------- ------------------------------------------------------------ ------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------------------- ------------------------------------------------------------ --------------------------------------- ---------------------------- -------------------- ------------------------------------------------------------ --------------------------------------- ---------------------------------------
8 A9F43420-E956-4CF2-B669-A75340C63479 0 SUSPENDED 2 MIRROR 1 2 FULL 1 TCP://Node01.testing.home:5022 NODE01 TCP://node03.testing.home:5022 1 CONNECTED 33000000014400001 10 NULL UNLIMITED 33000000014400001 33000000014400001
Then I removed mirroring from the principal:
principal:
database_id mirroring_guid mirroring_state mirroring_state_desc mirroring_role mirroring_role_desc mirroring_role_sequence mirroring_safety_level mirroring_safety_level_desc mirroring_safety_sequence mirroring_partner_name mirroring_partner_instance mirroring_witness_name mirroring_witness_state mirroring_witness_state_desc mirroring_failover_lsn mirroring_connection_timeout mirroring_redo_queue mirroring_redo_queue_type mirroring_end_of_log_lsn mirroring_replication_lsn
----------- ------------------------------------ --------------- ------------------------------------------------------------ -------------- ------------------------------------------------------------ ----------------------- ---------------------- ------------------------------------------------------------ ------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------------------- ------------------------------------------------------------ --------------------------------------- ---------------------------- -------------------- ------------------------------------------------------------ --------------------------------------- ---------------------------------------
20 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
mirror:
database_id mirroring_guid mirroring_state mirroring_state_desc mirroring_role mirroring_role_desc mirroring_role_sequence mirroring_safety_level mirroring_safety_level_desc mirroring_safety_sequence mirroring_partner_name mirroring_partner_instance mirroring_witness_name mirroring_witness_state mirroring_witness_state_desc mirroring_failover_lsn mirroring_connection_timeout mirroring_redo_queue mirroring_redo_queue_type mirroring_end_of_log_lsn mirroring_replication_lsn
----------- ------------------------------------ --------------- ------------------------------------------------------------ -------------- ------------------------------------------------------------ ----------------------- ---------------------- ------------------------------------------------------------ ------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------------------- ------------------------------------------------------------ --------------------------------------- ---------------------------- -------------------- ------------------------------------------------------------ --------------------------------------- ---------------------------------------
8 A9F43420-E956-4CF2-B669-A75340C63479 1 DISCONNECTED 2 MIRROR 1 2 FULL 1 TCP://Node01.testing.home:5022 NODE01 TCP://node03.testing.home:5022 1 CONNECTED 33000000014400001 10 NULL UNLIMITED 33000000014400001 33000000014400001
But again, I'm unable to remove mirroring from the mirror, with above posted error (CREATE FILE....)
October 26, 2012 at 3:21 pm
you should be able to remove the database using
DROP DATABASE yourdb
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 28, 2012 at 5:21 am
Nope, not even that is working:
Msg 3743, Level 16, State 1, Line 1
The database 'ifail2' is enabled for database mirroring. Database mirroring must be removed before you drop the database.
October 28, 2012 at 9:32 am
I've recreated this under 2012 and the only way i could remove the broken mirrored db was to stop the SQL Server service and delete the db disk files then drop the database once SQL Server restarted.
I've also recreated this under SQL Server 2008 so far and when switching off mirroring from the Principal the mirror database may then be deleted.
I think you should raise this behaviour with Microsoft, looks like it could be an issue in SQL Server 2012
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 28, 2012 at 2:01 pm
On the mirror that you cannot drop (but after it was already removed on the primary), what happens if you issue the following:
RESTORE <database_name> WITH RECOVERY
If it succeeds you should be able to remove it then.
Joie Andrew
"Since 1982"
October 28, 2012 at 2:47 pm
Joie Andrew (10/28/2012)
On the mirror that you cannot drop (but after it was already removed on the primary), what happens if you issue the following:RESTORE <database_name> WITH RECOVERY
If it succeeds you should be able to remove it then.
I recreated the scenario and tested that amongst others. The issue is, where as on SQL server 2008
ALTER DATABASE mydb SET PARTNER OFF
disables mirroring totally on the principal and the mirror, in 2012 the mirror thinks it's still part of a mirrored pair
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 29, 2012 at 7:33 am
tried it, the results were:
Msg 3104, Level 16, State 1, Line 1
RESTORE cannot operate on database 'ifail2' because it is configured for database mirroring or has joined an availability group. If you intend to restore the database, use ALTER DATABASE to remove mirroring or to remove the database from its availability group.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I've opened a ticked at connect.microsoft, let's see what they have to say 🙂
October 29, 2012 at 8:45 am
yes, mirror is switched off when indicated to do so under SQL Server 2008 but the mirror partner thinks that the mirror relationship is still in place under 2012
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 22, 2013 at 5:22 am
When mirror database is in following state
DB_Name (Mirror, Disconnected / In Recovery)
Issue following two commands to bring back database online
1) ALTER DATABASE <database name> SET PARTNER OFF
then it will be restoring state
DB_Name (restoring.....)
2) RESTORE <database_name> WITH RECOVERY
It is online now
October 22, 2013 at 8:37 am
Perry Whittle (10/28/2012)
I've recreated this under 2012 and the only way i could remove the broken mirrored db was to stop the SQL Server service and delete the db disk files then drop the database once SQL Server restarted.I've also recreated this under SQL Server 2008 so far and when switching off mirroring from the Principal the mirror database may then be deleted.
I think you should raise this behaviour with Microsoft, looks like it could be an issue in SQL Server 2012
I had the same issue, Call MS and let them know. I will try to find my case number and provide.
MCSE SQL Server 2012\2014\2016
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply