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

x
?
Solved

Sharepoint transaction logs and backups

Posted on 2013-11-07
5
Medium Priority
?
651 Views
Last Modified: 2013-12-01
My DB Server is running out of space due to sharepoint 2010 transaction logs. DB recovery is set to Full. Sharepoint is being backed up once a week and the transaction logs on every hour during the day.
If we are performing a full backup every week I would have presume the transaction logs would be commited to the DB at that point and from there on the tranaction logs should be quite small. In addition the DB recovery model could be set to simple and the most work that could be lost would be one hour. Can anyone advise on the best approach.
0
Comment
Question by:Sid_F
[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
  • 2
  • 2
5 Comments
 
LVL 15

Expert Comment

by:colly92002
ID: 39632334
You are probably not backing up the transaction log, just the databases.  This means that when using the full recovery model,  the log is never truncated, so grows for ever until the disk is full.  

Explanation of log truncation:
http://technet.microsoft.com/en-us/library/ms189085%28v=sql.105%29.aspx

How to backup the transaction log:
http://technet.microsoft.com/en-us/library/ms179478%28v=sql.105%29.aspx
http://technet.microsoft.com/en-us/library/ms191284%28v=sql.105%29.aspx
0
 
LVL 51

Expert Comment

by:Ted Bouskill
ID: 39637295
Turn off full recovery and switch all the databases to simple.  You cannot do partial restores of databases anyway because SharePoint won't support it and it will break more than it fixes.  Backups for SharePoint should and must be done using Central Administration tools.

Some databases can't EVER be restored without destroying SharePoint.

There are some 3rd party applications that will leverage transactions logs for doing targeted recovery that isn't covered by the SharePoint Recovery Bin however, if you aren't using those products, transaction logs are useless.

SQL DB backups of content databases (except the Central Administration) one is a potential recovery option, however, it's better to use the SharePoint backup tools.  You cannot backup/restore SharePoint configuration databases.
0
 
LVL 6

Author Comment

by:Sid_F
ID: 39637454
Ok maybe if I ask this a different way, right now my sharepoint database + log files are filling up my disk. DB is 70 gig and LDF is 25 gig, recovery mode is set to full.

I'm not experienced in SQL. I am trying to find out why this has occured. Is it because the backup software has not been setup properly or is it because Sharepoint has been misconfigured.
What is the correct way.
0
 
LVL 15

Expert Comment

by:colly92002
ID: 39638482
Its because you are not backing up your log files so in "full" database recovery model there is no process to stop them growing for ever - see my previous post for a full explaination and the differences between the models, and how you deal with each one.

tedbilly is also correct - SQL backups are not Sharepoint backups, therefore you can set the config  database (use the SQL manager gui) to "simple" model and then backup the database, and the logs will automatically shrink (which is what you are expecting to happen, but wont becuase your database is in "full" recovery model).  Personally I leave the content database in "full" mode and backup the transaction logs to shrink the files, since I like to have as many options available as possible should a catastophe occur.
0
 
LVL 51

Accepted Solution

by:
Ted Bouskill earned 2000 total points
ID: 39647079
Having the databases set to full recovery isn't a configuration error, it's just not effective for SharePoint.  For other situations it works great.  It just wasteful on SharePoint because of the way SharePoint is designed.

While SharePoint is running it has references stored in multiple databases along with values outside the databases.  Only the SharePoint backup tool can precisely backup all the persistent values correctly to ensure proper restoration.

I've had many catastrophes with SharePoint and the transaction logs have never helped.  Once you flip to the Simple model, then simply shrink the log file directly and it won't ever grow again.

I'd strongly suggest you search the Microsoft site for articles on best practices for SharePoint backup.
0

Featured Post

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This tutorial will walk an individual through the steps necessary to install and configure the Windows Server Backup Utility. Directly connect an external storage device such as a USB drive, or CD\DVD burner: If the device is a USB drive, ensure i…
This tutorial will show how to configure a new Backup Exec 2012 server and move an existing database to that server with the use of the BEUtility. Install Backup Exec 2012 on the new server and apply all of the latest hotfixes and service packs. The…

722 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