Solved

Transaction log in mirror too large

Posted on 2010-09-15
6
962 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server Configuration Manager WMI Error 11 42
partitioning database after decade growth 8 60
SQL QUERY 3 33
SQL- GROUP BY 4 25
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…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

730 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