October 7, 2008 at 11:27 am
I have Database Mirroring implemented on a couple of databases and the transaction log file is growing.
I've read in a few places that the only way to shrink the log file when database mirroring is in place is to remove mirroring, shrink the file and then configure mirroring again. Does anyone know of a better way to do this or is this the only way?
The problem I am having is that the DEV/QA/support servers that I restore these db's onto nightly do not have enough space for the large log file.
October 7, 2008 at 11:44 am
Are you running t-log backups? If not your transaction log will continue to grow.
I've never run a log shrink action on a mirrored DB. However, I have had to run data file drops and shrinks (shhh, don't tell anyone...). This caused major issues with the mirror.
October 7, 2008 at 2:35 pm
Yes I backup the log file every 15 minutes.
October 13, 2008 at 9:30 am
I guess that there is no other way to shrink the log files with mirroring enabled. I guess what I can do is setup a nightly job using scripts...
1.) Script to disable mirroring
2.) Script to shrink log files
3.) Script to enable mirroring.
If anyone has any other thoughts please let me know.
Thanks
Greg Goble
October 13, 2008 at 10:39 am
Shrinking files is a Bad Idea. If you are doing regular backups of your transaction log, then the size that it is at is the size it should remain. Otherwise, it will just grow again with the next day's set of operations.
The ONLY possible exception to this is if you had a one time only operation which caused the t-log files to grow to an unusual size. In that case a shrink *might* be a possible One Time Only solution. The more important solution would be to determin what that operation was and how to prevent it from growing the t-log file in the first place.
If what you need to do is copy data from production to a test/dev environment where you don't have as much space, you need to look at other solutions such as SSIS to copy a subset of the data over.
October 13, 2008 at 4:02 pm
I thank you very much for your reply. This may sound dumb but why is truncating/shrinking the log file a bad idea?
Without DB mirroring enabled when I backup the log file it is normally truncated anyway.
My Database is 10gb and My log file is around 75 GB 🙁
October 13, 2008 at 5:18 pm
Fragmentation issues, performance issues, stability issues...
Is your t-log continuing to grow or is it staying the same size? Have you looked at hos much space is actually occupied? You can run queries to determine this or right-click on the DB, go to Reports | Standard Reports | Disk Usage to get a graphical picture.
To me it sounds like you had / have some transactions that had / are having difficulties being fully transferred to / committed on the mirror. Have you looked at the mirroring monitoring tool (Tasks | Launch Database Mirroring Monitor)? What is your mirroring setup? Synchronous or Asynchronous? Do you have a witness?
October 14, 2008 at 5:38 am
If you backup a mirrored database log it should still truncate afterwards.
I agree it sounds as though the mirror is not synchronized , and thus the log is storing everything it hasn't committed to the mirror.
Try running
select * from msdb.sys.database_mirroring
if mirroring_state_desc does not = SYNCHRONIZED this is your problem.
September 15, 2009 at 8:57 am
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.
With normal maintenance (regular transaction log backups on the principle) the log file should be a stable size and shrinking or truncating would be unwanted. In cases similar to mine, a "one-time" (we hope) shrink was appropriate.
Student of SQL and Golf, Master of Neither
October 7, 2009 at 7:18 am
Hi ,
i had a doubt regarding log backups on a principal mirrored database.If the database state is not in
synchronized state and if i take a log backup , will i have to copy the same to the secondary database and apply the same there or will the changes etc in the log will be transferred to the seconday db.
Also what will be the case be if the log backup is taken if the db is in synchronized state and i take a log backup.will i have to apply the same again manually.
thanks for ur time.
October 7, 2009 at 9:06 am
Log backups on the principle, once database mirroring setup is complete and running, whether or not at the mirroring is 100% synchronized at the moment the backup is taken, have no effect on the process. The mirroring will continue, catch up eventually, and the primary db's log file will be marked as "mirroring finished" and the log space will be eligible for reuse.
Student of SQL and Golf, Master of Neither
March 14, 2011 at 4:03 am
To shrink transaction file for a database with mirror, you must backup as there are actives Virtual Log File :
http://www.xoowiki.com/Article/SQL-Server/tronquer-journal-de-log-sur-base-en-miroir-499.aspx
March 14, 2011 at 4:09 am
Please note: 2 year old thread
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 14, 2011 at 8:02 am
make sure your mirroring is synced and the CheckPoints have finished after the backup. We have had similar issues and it's always been one of those two things holding the space up.
The mirroring will not clear the record from the tlog without making sure it's been committed on the mirror. The service will not truncate the log until the checkpoint has finished.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply