IDG Contributor Network: Avoiding ginormous transaction logs with SharePoint databases

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.

To read this article in full or to leave a comment, please click here