Solved

SQL 2005/Mirror Shrink Log file

Posted on 2010-11-14
8
700 Views
Last Modified: 2012-05-10
Hello all,

I have a log file that grows very large and it is on a mirrored database.  Today I had to remove the mirror and shirink the log which grew to about 65 gig.   How do I manage keeping the log file to a normal size with the mirror setup.  So the principal database is the database that the log file is growing very large over time.
0
Comment
Question by:sbornstein2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 8

Expert Comment

by:infolurk
ID: 34133785
If you use the simple recovery model it truncates the log on backup.

Otherwise you will have to create a maintenance job to truncate the log regularly.
0
 

Author Comment

by:sbornstein2
ID: 34133804
You can't use simple recovery with mirroring, it has to be Full.
0
 
LVL 3

Expert Comment

by:krsreddy5
ID: 34133826
For database mirroring the database should be in FULL recovery model.

After establishing the mirroring you should schedule a job to take transaction log backup in regular intervals to avoid growing of log file.

K RajaSekhar Reddy
www.dbaarticles.com
FTTDBAS@dbaarticles.com
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 19

Accepted Solution

by:
Bhavesh Shah earned 500 total points
ID: 34133909
Mirroring won't affect the log, unless the Principal can't connect to the mirror in which case transactions will queue in the log and may cause issues.

 

Why are you shrinking your log? It should be as big as it needs to be, i.e. large enough to hold the transactions from your scheduled jobs. Having it auto-grow then re-shinking it is a waste of time and resources (it will slow down your scheduled jobs while the log grows).

 

The reason you can't shrink it sometimes is because the log actually consists of Virtual Log Files (VLFs). If the active one of these is near the end of the log file, it won't be shrinkable until it moves back to the beginning of the log file due to transactions having happened.

 

Normally the log file is smaller than the data file, but there may be exceptions. The bottom line is, the log needs to be as big as is required by the size and volume of transactions using the database.

 

Ensure you have adequate log backups of a suitable frequency all the time, and especially during your batch processing.

Soucre - http://www.bigresource.com/Tracker/Track-ms_sql-IzpjChR2/
0
 

Author Comment

by:sbornstein2
ID: 34133920
mirroring log shrinks don't seem to work so i need to know how to handle this with Full on and the log not growing exponentially all the time.  
0
 

Author Comment

by:sbornstein2
ID: 34133939
I just created a new mirror setup and ran some inserts to the database and already my database that has a size of 2 gig has a log of 4 gig right off the bat.
0
 
LVL 3

Expert Comment

by:krsreddy5
ID: 34133955
Hi,

as i said above you need to schedule the transaction log backup in regular intervals for mirrored database to avoid the log file from growing. Time interval between the log backups depends on transaction intensity on the mirrored database.

K RajaSekhar Reddy
www.dbaarticles.com
FTTDBAS@dbaarticles.com
0
 

Author Closing Comment

by:sbornstein2
ID: 34208843
thanks this is what I did find out something got out of synch
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question