Solved

Sharepoint transaction logs and backups

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Background Information Recently I have fixed file server permission issues for one of my client. The client has 1800 users and one Windows Server 2008 R2 domain joined file server with 12 TB of data, 250+ shared folders and the folder structure i…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…
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…

739 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