Move the T-log file of the Mirror Database

Categories: News, Professional, SSC, SSSOLV
Comments: 2 Comments
Published on: April 2, 2012

On more than one occasion, I have run into a case where a large database with a large transaction log was being mirrored.  Then a hiccup comes along – the transaction log on the primary server grows to a point where the transaction log on the secondary server fills the drive.  Now you have a problem and you need to reclaim some space or you need to find more disk.

Well, just because the database is mirrored and the drive is out of space, doesn’t mean there is nothing that can be done without impacting significantly the primary server – nor the mirror.

SQLCat has a nice write-up on what can be done.  I have used this method a few times, and felt that it needs to be brought up.  You can read the SQLCat article here.

In short (here are the steps from that article), you can do the following:

  1. On the secondary server, Use ALTER DATABASE MODIFY FILE to move the log file.

ALTER DATABASE <db_name> MODIFY FILE (NAME = LOG_FILE, FILENAME = ‘new location’).

  1. Stop the SQL Server Service for the instance which has the mirrored (secondary) database.
  2. Move the log file to the new location specified in the Modify File script already run.
  3. Start the SQL Server Service for the instance which has the mirrored (secondary) database.

Performing these steps can be just the trick needed to save the day.  Performing this move this way has saved me an outage on more than one occasion.  Also, this has saved me hours of work that could come along with having to break and rebuild the mirror.

2 Comments - Leave a comment
  1. Robert Pearl says:

    Nice tip, Jason! Going to share it on DBA.Connect, and other Linkedin groups….

Leave a comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">








Calendar
April 2012
M T W T F S S
« Mar   May »
 1
2345678
9101112131415
16171819202122
23242526272829
30  
Content
SQLHelp

SQLHelp


Welcome , today is Friday, October 31, 2014