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

x
?
Solved

Sharepoint transaction logs and backups

Posted on 2013-11-07
5
Medium Priority
?
710 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
  • 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

Independent Software Vendors: 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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
When using a search centre, I'm going to show you how to configure Sharepoint's search to only return results from the current site collection. Very useful when using Office 365 with multiple site collections.
This tutorial will walk an individual through configuring a drive on a Windows Server 2008 to perform shadow copies in order to quickly recover deleted files and folders. Click on Start and then select Computer to view the available drives on the se…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

971 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