Moving the Transaction log file of the Mirror Database

  • Article

 

Author: Sanjay Mishra
Reviewers: Prem Mehra, Mark Souza, Kun Cheng, Nicholas Dritsas, Thomas Kejser

 

Recently I worked with a customer who has deployed database mirroring for a very large database (~ 1 TB) between two data centers that are about 400 miles apart. I got an email from the customer that reads something like the following:

While establishing DBM, we restored the log file on the mirror to a different location than its corresponding location on the principal. We had to do it for some hardware constraints. We know it is not a best practice to have the transaction log files located differently on the principal and the mirror.

Now, we want to move the log file of the mirror database to its correct location, without having to re-establish DBM. We can’t afford any downtime either. Can you help? We have thought about the following options – some won’t work at all, and some won’t work for us because we can’t afford downtime to accomplish this task, and we don’t want to restore such a large database all over again.

Option 1 : Detach/Attach. Detach the mirror database, move the transaction log file to a different location, and then attach the database. This won’t work because a mirror database (being in the “restoring” state) can’t be detached / attached.

Option 2 : Perform a manual failover to switch the role of the mirror to principal, move the log file, and then perform another manual failover to switch the role back to mirror. While the log file is being moved from one location to another, the database will be down. We expect it will take several minutes to copy the file, and we can’t afford the downtime.

Option 3 : Take the mirror database offline. Copy the log to the correct location, and then alter the database to point to the new location for the log file. This won’t work because the mirror database (being in the “restoring” state) can’t be taken offline.

Option 4 : Break the database mirroring session, and re-establish mirror after performing restore to the correct location. Technically, this will work, but we would like to avoid taking backup of such a large database, copying it over network across 400 miles, and then perform a restore.

The Solution That Worked

We came up with the following solution which worked for the customer. To relocate the log file of the mirror database, follow these steps:

  1. On the mirror 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 instance which has the mirrored database.
  2. Move the log file to the correct location physically (use appropriate OS command).
  3. Start the SQL Server instance which has the mirrored database.

This can be done while the production workload is running on the principal. However, make sure that there is enough log space on the principal, because during the steps 2 through 4 above, the mirroring state will be DISCONNECTED (not just for this databases, but for all the databases on this instance). When the mirroring state is DISCONNECTED, The log records will not be sent from the principal to mirror, and will be accumulated on the principal. Once the mirror log file is moved and the mirror instance is started, the mirroring state will change to SYNCHRONIZING, and the principal will start sending the log records to the mirror.