Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Transaction log in mirror too large

Posted on 2010-09-15
6
Medium Priority
?
966 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 668 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 668 total points
ID: 33688952
0
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 664 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In case you ever have to remove a faulty web part from a page , add the following to the end of the page url ?contents=1
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

715 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