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
Solved

Sharepoint transaction logs and backups

Posted on 2013-11-07
5
484 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 500 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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

New Windows 7 Installations take days for Windows-Updates to show up and install. This can easily be fixed. I have finally decided to write an article because this seems to get asked several times a day lately. This Article and the Links apply to…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This tutorial will walk an individual through the steps necessary to enable the VMware\Hyper-V licensed feature of Backup Exec 2012. In addition, how to add a VMware server and configure a backup job. The first step is to acquire the necessary licen…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

840 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