Log File Issues with Mirroring

  • Del Murray

    Say Hey Kid

    Points: 679

    I have a DB on a remote server (production) that I am mirroring to a local server for high availability (backup). The log file on the prod DB is large which is my fault because I need to run regular log backups on that server and forgot to do so. Like many of you, I'm the programmer, DBA, sweep the floors and talk to the users while the marketing guys get to drink beer, schmooze with the secretaries and play Shoots and Ladders on their PC.

    However, that log file on the mirror for that DB has also grown quite large. Since that DB is in a "recovering" state, how can I backup the logfile on the mirror to shrink it. I used an older server that didn't have a lot of disk space and now I am sorry... the server cant accept any more expansion of the log file and mirroing is suspened. I have broken the mirror but am not sure how to get the mirror database out of "restoring" mode so that I can back it up, backup the log file and start over again. I tried shrinking the log file on production while the mirror was running. THat worked fine and I got a smaller log. I copied that log to the mirror and tried to restore it to the mirror only to find out that the checkpoints were out of sequence. That made sense because mirroring was suspended due to space problems. I would have thought though that the backup on the production log would have gotten all the "unmirrored" log records into the logfile so that a resotre of the log file at the mirror would have been successful.

    Question How do I shrink a log file on a mirror (not the principal)?

    Thanks is advance for any sage help. I will probably have to restore the mirror and restart mirroring from scratch so I know that will get me out of the woods, but I'm concerned about this happening again. If the logfile hadn't grown so large on the mirror I would not have had the space problem .. its a catch 22.

    TIA,

    Del

  • Del Murray

    Say Hey Kid

    Points: 679

    Ok .. after a bunch of research ... does this make sense.

    Wait until both Principal and Mirror are in "synch".

    Pause Mirroring.

  • Del Murray

    Say Hey Kid

    Points: 679

    Ok .. after a bunch of research ... does this make sense.

    1 Wait until both Principal and Mirror are in "synch".

    2 Pause Mirroring.

    3. Backup Principal Log and truncate while doing so and free up unused space.

    4. Copy the log file backup from 3 (above) to the mirror server and make it the same name as the currently used logfile. (Replace the big xxx.log with little xxx.log)

    5. Restore the logfile (from 4) and be sure to leave the database in "restoring" state.

    6. Resume the mirror ...

  • Mohit K. Gupta

    SSChampion

    Points: 12130

    If you broke the mirror you'll have to rebuild it anyways. Because both databases will be out of sync as soon as you truncate the log.

    If Mirror is broken already ...

    1) Delete database on Mirror serer.

    2) Truncate/shrink log file to desired size (or size needed, if it grew once it will grow again).

    3) Setup mirroring again.

    4) Implement Full backup/T-Log backup.

    If mirroring not broken

    - When you backup the transaction from Principle server those transactions are also marked as expired on mirror partner.

    - If you shrink the physical log on principal it should carry over to the mirror automatically (not recommended).

    Thanks.

    Mohit.

    [font="Arial"]---

    Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
    Microsoft FTE - SQL Server PFE

    * Some time its the search that counts, not the finding...
    * I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

    How to ask for help .. Read Best Practices here[/url].

  • Mohit K. Gupta

    SSChampion

    Points: 12130

    Del Murray (3/26/2009)


    4. Copy the log file backup from 3 (above) to the mirror server and make it the same name as the currently used logfile. (Replace the big xxx.log with little xxx.log)

    5. Restore the logfile (from 4) and be sure to leave the database in "restoring" state.

    If I am understanding you right you are saying you will delete the ldf file for the mirror database and replace it with ldf file from principal; that should not work. Not recommend and I don't think it is possible.

    Thanks.

    [font="Arial"]---

    Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
    Microsoft FTE - SQL Server PFE

    * Some time its the search that counts, not the finding...
    * I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

    How to ask for help .. Read Best Practices here[/url].

  • Mohit K. Gupta

    SSChampion

    Points: 12130

    Ohh you asked how to recover database after you broke mirroring ..

    RESTORE DATABASE DBName WITH Recovery

    How to break mirror:

    ALTER DATABASE DBName WITH PARTNER OFF

    Ref: http://sqllearnings.blogspot.com/search/label/Database%20Mirroring

    [font="Arial"]---

    Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
    Microsoft FTE - SQL Server PFE

    * Some time its the search that counts, not the finding...
    * I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

    How to ask for help .. Read Best Practices here[/url].

  • Del Murray

    Say Hey Kid

    Points: 679

    Mohit ..

    Are you sure that if I backup the Log with a Logbackup on the Principal that the truncation of the Principal Log will be reflected in the Mirror Log ??? That sort of implies that backing up a logfile is recorded in the logfile ... I'm no sql server guru but that would be amazing to me.

  • Mohit K. Gupta

    SSChampion

    Points: 12130

    Del Murray (3/27/2009)


    Mohit ..

    Are you sure that if I backup the Log with a Logbackup on the Principal that the truncation of the Principal Log will be reflected in the Mirror Log ??? That sort of implies that backing up a logfile is recorded in the logfile ... I'm no sql server guru but that would be amazing to me.

    When you backup a log file all completed transactions in the transaction log file are marked for overwrite/empty space. So the transaction log file is not recording the backup was done; but it recording the records can be over written. Since a change is taking place in transaction log file it is mirrored over to the other server.

    Principal Server Mirror Server

    1:Record 1 [c] -------------------------> Record 1 [c]

    2:Record 2 [c] -------------------------> Record 2 [c]

    3:empty space -------------------------> empty space

    4:empty space -------------------------> empty space

    5:Record 3 -------------------------> Record 3 You initiate a backup ... lets assume they don't get copied over ...

    now our T-Log files are out of sync. And we know the T-Logs have to be in sync

    when we start mirroring becaus LNS numbers have to line up.

    Principal Server Mirror Server

    1:empty space -------------------------> Record 1 [c]

    2:empty space -------------------------> Record 2 [c]

    3:empty space -------------------------> empty space

    4:empty space -------------------------> empty space

    5:Record 3 -------------------------> Record 3

    So this is why I think it is like this ...

    Principal Server Mirror Server

    1:empty space -------------------------> empty space

    2:empty space -------------------------> empty space

    3:empty space -------------------------> empty space

    4:empty space -------------------------> empty space

    5:Record 3 -------------------------> Record 3 This is observed affect; I have never had to backup/shrink files on Mirror server and the T-Log on Mirror server never have grown past the principal.

    [font="Arial"]---

    Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
    Microsoft FTE - SQL Server PFE

    * Some time its the search that counts, not the finding...
    * I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

    How to ask for help .. Read Best Practices here[/url].

  • Del Murray

    Say Hey Kid

    Points: 679

    Mohit .. you are right ...

    I backedup the log on Principal. Then I did a "shrink File" on Principal and the new log was much much smaller and the mirror log file was shrunk also ..

    Very cool.

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply