Solved

Transaction log in mirror too large

Posted on 2010-09-15
6
965 Views
Last Modified: 2012-05-10
Hi I have a large farm with about 5000 users and the transaction log of my SharePoint 2010 DB is growing large. All of my SharePoint databases are in mirror . How can shrink automaticly the log file? Is it possible in mirror to stop the growing ? The sql server is 2008 R2 Standard
0
Comment
Question by:majox
[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
6 Comments
 
LVL 12

Accepted Solution

by:
geowrian earned 167 total points
ID: 33688918
The transaction log is part of the bulk-logged and full recovery models. This allow recovery from a backup to a specific point in time. If this is not necessary in your DR plan, you can use simple mode recovery.

Either way, the way to reduce the size of the transaction log is to regularly backup the transaction log file. Upon successful backup, it will mark the all space in the file as available. Any new transaction logging will use this available space until depleted. Also, performing a shrink on the transaction log file will release any available space int he file and shrink it.

However, the process of shrinking leads to fragmentation, so it is generally recommended to only be performed when necessary, and performed manually.

The best way to prevent a large transaction log file is to perform a backup of the transaction log very regularly. For instance, back it up every 30 minutes (allowing a catastrophic event to only lose up to 30 minutes of data). Others may do this hourly, daily, or every 5 minutes depending on their needs. This way the log never grows too large as it keeps reusing the same space.
0
 
LVL 7

Assisted Solution

by:rashmi_vaghela
rashmi_vaghela earned 167 total points
ID: 33688952
0
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 166 total points
ID: 33688973
>> All of my SharePoint databases are in mirror . How can shrink automaticly the log file? Is it possible in mirror to stop the growing ?

Transaction log files will shrink after a Transaction Log backup in Principal server.
Hence scheduling Transacitonal Log backup every 15 or 30 minutes in Principal Server would help keep log file size in control for both Principal and Mirrored database.
0
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 
LVL 12

Expert Comment

by:geowrian
ID: 33689008
>>Transaction log files will shrink after a Transaction Log backup in Principal server.
Hence scheduling Transacitonal Log backup every 15 or 30 minutes in Principal Server would help keep log file size in control for both Principal and Mirrored database.

Just a note that they will shrink after a backup of the transaction log on the principal server, along with an initial shrink to get the file down to a small size (otherwise the transaction log will remain large but have a lot of free space in it).
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 33689136
>> Just a note that they will shrink after a backup of the transaction log on the principal server, along with an initial shrink to get the file down to a small size

Yes, it is what I meant and the logs occupied by unsynchronized transactions would not be freed up till it is synched to Mirror db.
0
 
LVL 1

Author Comment

by:majox
ID: 33874894
Thanks guys after one month test it works :)
0

Featured Post

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard 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.
These days socially coordinated efforts have turned into a critical requirement for enterprises.
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

636 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