Solved

Transaction log in mirror too large

Posted on 2010-09-15
6
960 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 167 total points
Comment Utility
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
Comment Utility
0
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 166 total points
Comment Utility
>> 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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 12

Expert Comment

by:geowrian
Comment Utility
>>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
Comment Utility
>> 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
Comment Utility
Thanks guys after one month test it works :)
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

We had a requirement to extract data from a SharePoint 2010 Customer List into a CSV file and then place the CSV file into a directory on the network so that the file could be consumed by an AS400 system. I will share in Part 1 how to Extract the Da…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now