Solved

SQL 2005/Mirror Shrink Log file

Posted on 2010-11-14
8
704 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

691 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