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
Solved

SQL 2005/Mirror Shrink Log file

Posted on 2010-11-14
8
697 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
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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

860 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