Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Database Mirroring FAQ: How do we handle transaction log maintenance for a mirrored database?

Question: How do we handle transaction log maintenance for a mirrored database?

This question was asked on a technical discussion group. My reply follows.

Hi,

My customer would like to know how to handle the txn log maintenance with DB Mirroring? They normally truncate the txn log after a full backup without DB Mirroring. But with DB mirroring, can they truncate the txn log? What happen at the mirror DB if the txn log is truncated? Do they need a different maintenance plan at the mirror DB?

My answer:

They shouldn’t be routinely truncating the transaction log whether they are using database mirroring or not. They need to perform regular log backups and that should be it. The more frequent, the better. At least every half hour. If the system is very active, then every 15 minutes or every 5 minutes.

Don’t use the database maintenance plan for the log backups because it will try to backup up the database if it is the mirror and will fail because the database is not online. You want to create a job that uses T-SQL to query for databases in the online state and only backup those databases that are online.

Reply back:

Hi Robert,

My customer is performing a daily fully backup and log backup every 2 hours, after the nightly fully backup, they will truncate the log to maintain the size of the log file. If they don’t clean up the log, the log file will growth extremely fast and eat up the disk space.

What is the recommendation to maintain the size of the log file if the suggestion is not to routinely truncate the log? How about the size of the log file growth at the Mirror DB? Will that be the same size as of the Primary DB?

My answer:

Truncating the log file repeatedly is bad. It degrades performance of the log file over time as it causes SQL to create lots of virtual log files as the size grows back up. They should have never started this practice. I’m not saying that there is never a legitimate reason to truncate the log file, but using it instead of log backups is a very bad practice.

They key to maintaining the size of the log file is frequent log backups. Every 30 minutes would be the lowest frequency I would use. If their system is very active, then they should run log backups every 15 minutes or every 5 minutes.

Reply back:

Hi Robert,

When a log backup is applied to the production site, what would be applied to the txn log file the Mirroring DB?

My answer:

When you backup the log on the principal, the virtual log files (individual units within the log file) are marked as re-writable. The same VLF’s are marked as re-writable in the mirror log file as well. The VLF status is mirrored on the database.

Comments

Posted by Amy Herndon on 3 June 2009

Database Mirroring FAQ: How do we handle transaction log maintenance for a ... They normally truncate the txn log after a full backup without DB Mirroring. ...

www.sqlservercentral.com/blogs/robert_davis/archive/.../How-do-we-handle-transaction-log-maintenance-for-a-mirrored-database.aspx - Similar pages

I am trying to see what we can do to truncate the logs on the morrroed dbs.

Thanks.

Posted by Robert Davis on 4 June 2009

Hi Amy. Take a look at this KB article if you need to shrink the log file on the mirror: support.microsoft.com/.../937531

Otherwise, if you just need to shrink the log file on the principal, use DBCC ShrinkFile().

Posted by Robert Mills on 9 June 2009

Robert,

The article you referenced from Microsoft Support is a very bad thing for my implementation.  I'm dealing with a VERY LARGE DB and it is NOT set to autogrow.  I really don't want to shrink it even if that would shrink my mirror transaction log.  Is there any way known in SQL 2005 to truncate / shrink a transaction log ONLY on the mirror?

thanks,

Bob Mills

Posted by Robert Davis on 11 June 2009

Hi Bob. I agree about it being a bad thing. That should only be used for extreme cases, in my opinion. Unfortunately, it was required due to a bug if your SQL build was SQL 2005 prior to CU6 for SP2. Fortunately, that bug has been fixed!!

Really, the only way to shrink the log file on the mirror only would be to fail over to the mirror, shrink the file, and then fail back.

Posted by -- Cranfield on 5 August 2009

We've seen a problem where if the log file on the Primary autogrows then this growth is not propogated to the mirror.  This causes a big problem in that the log file on the primary will not truncate after the log backup. I'm sitting with a tran log of 150GB with 65% used. There are no open transactions and a backup or checkpoint does not truncate the log.  My log file on my mirror is only 80GB. the mirror is in synch.  This has to be a bug.

Posted by BobAtDBS on 15 September 2009

If you simply use DBCC ShrinkFile on the principle's log file, the resulting change will be applied to the mirror as well in a very small transaction.  The mirror size will normally stay perfectly in synch with the principle's size.

We ran out of disk space on the mirror, so mirroring was suspended and the principle's log grew rather large.  After clearing room on the mirror's log drive, resuming mirroring, letting it catch up, and finally letting the next principle log backup take place, we were able to use DBCC ShrinkFile on the principle's log file and the change was instantly reflected on the mirror.

Posted by Anonymous on 30 March 2010

Pingback from  SQL Spiegelung und Transaktionsprotokoll - MCSEboard.de MCSE Forum

Leave a Comment

Please register or log in to leave a comment.