SQL 2005/Mirror Shrink Log file

Posted on 2010-11-14
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.
Question by:sbornstein2

Expert Comment

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.

Author Comment

ID: 34133804
You can't use simple recovery with mirroring, it has to be Full.

Expert Comment

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
LVL 19

Accepted Solution

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 -
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).


Author Comment

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.  

Author Comment

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.

Expert Comment

ID: 34133955

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

Author Closing Comment

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

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now