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:
- 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’).
- Stop the SQL Server Service for the instance which has the mirrored (secondary) database.
- Move the log file to the new location specified in the Modify File script already run.
- 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.
