Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Transaction log in mirror too large

Posted on 2010-09-15
6
Medium Priority
?
968 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
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
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.

 
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

Note:  There are two main ways to deploy InfoPath forms:  Server-side and directly through the SharePoint site.  Deploying a server-side InfoPath form means the form is approved by the Administrator, thus allowing greater functionality in the form. …
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 a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

824 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