Solved

Sharepoint transaction logs and backups

Posted on 2013-11-07
5
431 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
Comment Utility
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:tedbilly
Comment Utility
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 5

Author Comment

by:Sid_F
Comment Utility
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
Comment Utility
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:
tedbilly earned 500 total points
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
A safe way to clean winsxs folder from your windows server 2008 R2 editions
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles to another domain controller. Log onto the new domain controller with a user account t…
This tutorial will walk an individual through setting the global and backup job media overwrite and protection periods in Backup Exec 2012. Log onto the Backup Exec Central Administration Server. Examine the services. If all or most of them are stop…

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now