Avoiding ginormous transaction logs with SharePoint databases

Find out how organizations avoid extremely large transaction log (.ldf) files.

microsoft sharepoint logo

There isn't a week that goes by that I don't hear or read about an organization seeing ginormous transaction log (.ldf) files, while the associated data (.mdf) file is at the size it should be when considering the amount of data in SharePoint.

This is a common problem that can be solved simply by backing up your transaction logs (T-logs). It really is that easy. So if it is that simple, why do we see these humongous transaction logs so often? I am going to blame it on a "misunderstanding" of how Microsoft describes a full database backup as seen below:

"Full backup -- A data backup that contains all the data in a specific database or set of filegroups or files, and also enough log to allow for recovering that data." This definition can be found here. This is a bit confusing because it kinda, sorta sounds like when you perform a full database backup that your transaction log also is backed up, and more importantly truncated, which will reduce the size of the log file. This is NOT TRUE! If we want to minimize the size of our T-logs we have to specifically back them up which truncates them. We have to perform this backup of the transaction log using either SQL Server Management Studio (SSMS) or T-SQL.

Well, let me stop for a minute… because there is another way we can prevent log files from becoming huge -- and that is by setting the database recovery model to Simple mode. But I DON'T want you to do that in a production environment because you eliminate the ability to recover data from the transaction logs. Yes, that is true! If you put your databases in Simple Recovery Model, it tells the SQL Server CHECKPOINT process to write the transactions from the T-log to the data file and after a successful write, to purge them out of the transaction log file.

Now, it is true that this will avoid these over-sized transaction log files, but it is also true that you will not be able to use the transaction logs in a data recovery scenario because they log files are empty. No data, nada, zilch that can be used for data recovery. For additional information on the recovery models, see this site.

So what do we do? How do we avoid the use of Simple Recovery Model so that we can use the T-logs for data recovery, but not let them grow to be crazy huge? Well, I already mentioned the need to periodically back up the transaction logs, which also purges the contents of the log files and avoids the monster-sized log files.

The following diagram is an example of what we can do to maintain a data recovery option, yet, avoid super-sized transaction log files.

transaction log file diagram

This suggested backup strategy involves three tasks that allow you to maintain the transactions in the T-logs for data recovery while minimizing the size of your log files.

1.    Be sure databases are set to Full Recovery Model.
2.    Perform regularly scheduled full backups of your data files on Sunday night.
3.    Perform regularly scheduled backups of your log files on Sunday night after a successful backup of your data file.

We'll use the Full Recovery Model so we can perform a back up of the Thursday transactions, located in the transaction logs, in the event we lose the data in the data (.mdf) file. We will also schedule full backups of the data files on a regular schedule, which will be immediately followed by a back up of the transaction logs so they get purged and don't become so BIG.

So to avoid ginormous transaction logs; don't use Simple Recovery Model, simply periodically back up the transaction logs so they don't get too large, and the best time to do this is immediately after a full back up of the data file (.mdf), which as you can see we are performing on Sunday night. This strategy provides the best of both worlds, data recovery and properly-sized transaction log files.

Brian is a Microsoft MVP who has specialized in Office 365, SharePoint and SQL Server integration over the past few years, but his breadth of knowledge also includes Microsoft operating systems and Active Directory. He is also the founder and CEO of MicroTechPoint, and you can find all of his IT training videos at MicroTechPoint.com.

Copyright © 2015 IDG Communications, Inc.