Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL 2005/Mirror Shrink Log file

Posted on 2010-11-14
8
Medium Priority
?
715 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
Independent Software Vendors: 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 2000 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

Independent Software Vendors: 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!

Question has a verified solution.

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

There are some very powerful Dynamic 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 di…
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…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

609 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