SQL Server Performance

Can't turn off mirroring

Discussion in 'SQL Server 2005 Database Mirroring' started by ccbryan, Aug 7, 2007.

  1. ccbryan New Member

    I had High Protection mirroring up and running on two databases between two servers (call the live server A and the mirror B). There was no witness server. I did not have proper log size control procedures on the live server and ended up with a log file that couldn't be backed up for lack of disk space and as a result (at least I'm pretty sure it was as a result of that) my mirroring session disconnected.
    When I attempted to reinitialize mirroring I opened my databases on A and stopped the mirroring session and removed mirroring from the properties of those databases. I am now trying to drop the mirror databases on server B so that I can delete them and replace them with current restores and start a new mirroring session. However, each time I attempt to alter them in any way I get the following error:
    Cannot show requested dialog. (SQLMgmt)
    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SQLServer.Connectioninfo)
    Database [db name] is enabled for database mirroring, but neither the partner nor witness server instances are available: the database cannot be opened. (Microsoft SQL Server, error: 955)
    Alternatively, depending on what I'm trying to do, the third line reads:
    Database [db name] is enabled for Database Mirroring, Database Mirroring must be removed before dropping [or whatever] the database. (Microsoft SQL Server, error: 3743)
    It won't let me open the properties window of either database... how can I turn off mirroring so that I can get rid of these Server B databases and get a new session going?

    Thanks in advance for any thoughts!
  2. Akthar New Member

    maybe you can try :
    Restore Database mirrordb WITH RECOVERY... on the mirrored server
  3. satya Moderator

    From Technet link:
    Assume you have configured database mirroring with a witness. When the mirror is unavailable, the principal runs exposed. While the mirror is unavailable, if the witness is also lost, the principal becomes isolated and can’t service the clients. Even though the principal database is running, it is not available to the clients. If you attempt to connect to the database, you get the message “Database <dbname> is enabled for database mirroring, but neither the partner nor witness server instances are available: the database cannot be opened.”
    If the mirror or the witness cannot be brought back online quickly, then the only way to resume database service is to terminate the database mirroring session. To do this, you execute the following command after connecting to the master database of the principal server:
    ALTER DATABASE <db_name> SET PARTNER OFF
    Note that you need to execute a SET PARTNER OFF command, and not a SET WITNESS OFF. SET WITNESS OFF will not work in this situation.
  4. ccbryan New Member

    Thanks Satya and Akthar. It's now resolved. I had to use both your suggestions: first alter database set partner off, then restore with recover.
    I appreciate the quick and useful replies!

    Chandler

Share This Page