locked
Stopping and Starting Mirroring RRS feed

  • Question

  • My problem is primarily with SQL backup but the instant question is with mirroring in SQL 2005.  I have a log file that is out of control and I need to do a backup with truncate_only, but this is command is evidently disallowed while mirroring is enabled.  Is it possible to temporarily deactivate or remove mirroring in order to get the backup log under control?  Is it possible to do that and then 'resume' mirroring?

     

    A secondary issue is that I don't think mirroring is actually working at this point in time anyway.  In the Mirroring section of the database properties the Status window says "No connection: cannot connect to the mirror server instance," so the question of stopping and restarting may be moot.

     

    How does one reestablish mirroring if the connection is lost?

     

    Lots of questions, I know...  I'm reading up on it, but I'm obviously not the guy who set it up in the first place!  Thanks in advance for any help...


    CB

    Wednesday, July 25, 2007 3:54 PM

All replies

  • OK, it looks as though I simply need to stop the mirroring session, backup truncate and shrink my logs, and then start a new mirroring session.  Thanks anyway.  See, I said I was reading up...


    CB

    Wednesday, July 25, 2007 6:32 PM
  • i am not sure about the truncate_only command while mirroring is enabled.......but as you pointed out currently there is no mirroring going on........the "No connection" says that that currently there is no mirroring taking place.....may be the principal server may have been not reachable and hence the mirroring is disconnected.....or in this case the mirror server may be down..........so you need to bring the mirror server up and once it is done i think mirroring will automatically establish connection.If your mirror server B is down and you have only principal A and witness W consider the following,

    In High Availability mode, when the mirror Server B fails, no failover occurs.

    No automatic failover will occur and the partners will not exchange roles. When Server B is restored, all three servers return to their original roles and states.

    Note that the session is exposed without a mirror, because the data is not being placed in redundant databases.

    As soon as you can restore Server B, it will resume its mirror role and when the two partners are synchronized, the mirroring session is no longer considered exposed.

     

     so if its high availability the mirroring will continue automatically once the mirror server is up.....

    In High Availability mode, when the principal Server A fails, a failover occurs.

     After the principal server A is lost, the mirror and the witness servers form a quorum and an automatic failover occurs. If you are able to bring back the original principal server, then it will take on the mirror role.

    but in High protection mode, when the mirror server B is down the principal remains unaffected.....but once the mirror is up it gets automatically synchronized.........but in this mode since the safety is full the principal will wait for hardening(commiting) its transactions on the mirror and to receive and acknowledgement...........but since the mirror is down the ack cannot be sent and hence the log file of principal might grow.........

     

    Thursday, July 26, 2007 1:54 AM
  • Stopping & Removing Database Mirroring are 2 different topics. In order to truncate the log you need to remove mirroring. Even if you stop the Database Mirroring session by issuing the below command, the database TLOG cannot be truncated using the BACKUP LOG with no_log / Truncate_ONLY command. The only option is to take a log backup to a drive with sufficient space and then perform shirnk file.

     

    ALTER DATABASE <db name> SET PARTNER  SUSPEND

     

    Second option is to remove the database from mirroring and perform backup log with no_log/truncate (Not recommended). and perform Shrink file.

     

    Hope this helps.

     

    Vishal

     

     

     

     

    Monday, July 30, 2007 7:06 AM
  • Monday, July 30, 2007 7:09 AM