SQL Server Performance

Cannot Switch to Secondary in mirroring

Discussion in 'ALL SQL SERVER QUESTIONS' started by Wayne Nguyen, Feb 4, 2013.

  1. Wayne Nguyen New Member

    Dear Expert,

    I have 2 database servers using mirroring without witness server.
    Unluckily, at the midnight, power supply to server room is off, UPS cannot work well, so my 2 servers is power off suddenly.

    After power on, i check my primary server database is in suspect status, the secondary is in recovery.

    So i try to recover primary as some tip on the net but it said the mirroring is currently active.
    I try to run SET PARTNER OFF but unsuccessful.

    The secondary i try to run SET PARTNER OFF but i get the same result. And it said it get error in Transaction Log, so i cannot recover it.

    Please help me to solve this problem.
  2. Shehap MVP, MCTS, MCITP SQL Server

    First welcome to Forums,

    Such errors can be related to Transaction log file size that can delay tangibly the recovery process if log file was huge so it is recommended to schedule adequate maintenance jobs (Truncate Log files and shrink) to restrict the inflation of log files

    In the mean time, please advise about that transaction log error to be able to figure out the right way to exist out of this case
  3. Wayne Nguyen New Member

    Thank for your reply.

    Currently, I don't have this error log. So i solve it by delete then restore this database with last backup for users continue to work.
    So I think turn on the option High Safety in Mirroring. Can it prevent the error both of primary and secondary in the same time?
  4. Shehap MVP, MCTS, MCITP SQL Server

    High safety is just a matter of data synchronization either synchronous or non-synchronous which doesn’t matter for our current case coz errors of principal server are dependent on log file errors and thus it is still in recovery mode whereas the other mirror server is dependent on the principal and thus it is at suspect mode and nothing can be done easily except using ALTER DATABASE database_name SET PARTNER OFF or it might be much faster to restore DB much from the most recent backup

    Please let me know if any further help is needed
  5. satya Moderator

    How big are the database transactions?
    Irrespective of database mirroroing or not the consistency is based on the recovery interval option.
    See what option has been set on RECOVERY INTERVAL within that database, see BOL:
    Recovery time is determined by how much work has been done since the last checkpoint, and by how much work has been done by all active transactions at the time of the data loss. SQL Server uses a configuration option called recovery interval to set the approximate maximum number of minutes per database that SQL Server needs to recover databases. This recovery interval setting controls checkpoint frequency. For an online transaction processing (OLTP) system (using short transactions), recovery interval is the primary factor determining recovery time.

Share This Page