Solved

Log size for SharePoint database

Posted on 2014-02-10
10
351 Views
Last Modified: 2014-02-16
I find that there are several big log files on SharePoint,  can I use the normal SQL backup procedure to backup the log with truncate, and then shrink the transaction log file.

In a long run, can I change the recovery mode for these SQL database to Recovery ? Will there be any concern on this ?  

Tks

SharePoint_Config
SharePoint_AdminContent_xxxx
WSS_Content
SSISDB
0
Comment
Question by:AXISHK
[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
  • 5
  • 5
10 Comments
 
LVL 29

Expert Comment

by:QPR
ID: 39849153
Yes, backup the logs (if not in simple mode) then backup with truncate.
The recovery mode you select depends on what data you can afford to lose in a disaster.
Full mode will give you the ability to restore from backup (DB backup) and then restore all transaction log backups taken since the DB backup.
Simple mode means you can only restore the DB backup as there are no log backups.

I would put the content databases in full and others in simple.
Have a nightly DB backup for all and an hourly log backup for the DBs in full recovery mode.

While in the area check that the autogrow value on the files are not too small. Larger values with cut down on (CPU intensive) auto growths and reduce VLFs (virtual log files) within the logs
0
 

Author Comment

by:AXISHK
ID: 39849201
One more check,

Can I directly shrink the transaction log without doing the backup with truncate option, or just doing the backup without truncate option ? What's that option used for ?

Tks
0
 
LVL 29

Expert Comment

by:QPR
ID: 39849238
Backup without truncate won't shrink, DBCC shrinkfile will, backup with truncate will
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:AXISHK
ID: 39849290
Sorry,

Do u mean that I can issue DBCC shrinkfile on the transaction log and it will truncate the committed transaction and reduces the physical size of the log file ?
0
 
LVL 29

Expert Comment

by:QPR
ID: 39849294
I would truncate follow by DBCC shrinkfile to be sure
0
 

Author Comment

by:AXISHK
ID: 39849324
We can only truncate the transaction log through the backup, correct ?

The truncate and shrinkfile works on transaction log only, no need to carry out on the database, correct ?

Tks
0
 
LVL 29

Expert Comment

by:QPR
ID: 39849345
Shrinkfile also works on the DB file too.
0
 

Author Comment

by:AXISHK
ID: 39850415
HOW TO Shrink database?
Can i truncate trx. log directly or it can only be truncate through backup?
0
 
LVL 29

Accepted Solution

by:
QPR earned 500 total points
ID: 39850997
DBCC shrinkfile
http://msdn.microsoft.com/en-us/library/ms189493.aspx

All you need to know about shrinking the DB, the log and truncating
0
 

Author Closing Comment

by:AXISHK
ID: 39863672
Tks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Migrating SharePoint ("Companyweb") site from SBS2011 to stand-alone server 2 148
Sharepoint KB 2 98
Licensing Sharepoint 2016 for developers 11 97
Regarding Disk IO 3 88
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…
I thought I'd write this up for anyone who has a request to create an anonymous whistle-blower-type submission form created using SharePoint 2010 (this would probably work the same for 2013). It's not 100% fool-proof but it's as close as you can get…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

734 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