LDF SQL files have grown too big on MOSS 2007 Databases

Posted on 2010-01-11
Medium Priority
Last Modified: 2012-05-08
I'm currently running a MOSS 2007 Server farm running on Windows 2003 and SQL 2005. Recently, I've reviewed my databases and was shocked to find the database log files (ldf) to have grown way out of proportion compared to the MDF files. See below, some of my file sizes.

11/12/2009  11:03 PM        10,682,368 WSS_Config_SharePoint.mdf
12/26/2009  11:05 AM    56,539,676,672 WSS_Config_SharePoint_log.LDF

01/11/2010  05:28 PM    12,722,569,216 WSS_Content_Intranet.mdf
11/12/2009  11:03 PM    31,915,048,960 WSS_Content_Intranet_log.LDF

11/12/2009  11:03 PM     1,223,884,800 WSS_Content_MySites.mdf
12/29/2009  03:40 AM    91,057,750,016 WSS_Content_MySites_log.LDF

12/31/2009  03:49 PM    20,979,056,640 WSS_Content_Programs.mdf
12/10/2009  11:50 AM    68,413,030,400 WSS_Content_Programs_log.LDF

My first question is, how do I reduce the size of the LDF in some of the databases? I've already tried a simple "Shrink Database" with no luck. Secondly, what can I do to keep this under control going forward in the future?
Question by:daramooka
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
LVL 26

Expert Comment

ID: 26288900
back up your databases tarnsactions.. that will truncate the log files.
LVL 16

Accepted Solution

quihong earned 668 total points
ID: 26289171
use <DatabaseName>
backup log <DatabaseName> with truncate_only
dbcc shrinkfile(<LogFileName>, 1)

so something like:

use WSS_Config_SharePoint
backup log WSS_Config_SharePoint with truncate_only
dbcc shrinkfile('WSS_Config_SharePoint_log', 1)
LVL 43

Assisted Solution

by:Eugene Z
Eugene Z earned 664 total points
ID: 26289379
if you do not need a poin-of-time recovery
switch databases recovery  mode to simple from full than

INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE

How to stop the transaction log of a SQL Server database from growing unexpectedly

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.


Author Comment

ID: 26289438
EugeneZ, some of the databases in question are currently in Simple mode. But I suspected that it may be a combination of a few things. I've been using Symantec Backup Exec (using the Sharepoint Plugin) to back up the Sharepoint Farm. And well, I think I should stick with stsadm going forward to help avoid this problem. I'll read the article, as well as try the SQL query to see where it get's me and update everybody once that's done. However, I'm hoping that some Sharepoint guys out there might be able add some input. As said, all in all, I'm thinking I want to just write a really good script to handle this stuff in the future.
LVL 43

Expert Comment

by:Eugene Z
ID: 26289626
btw: what  service pack are you running on your sql server 2005? needs to be fresh (e.g. sp3)
and do you run translog backups on db with full recovery ( try to backup see posts above..)

you need to review your databases usage\transactions\code, etc.


LVL 44

Assisted Solution

by:zephyr_hex (Megan)
zephyr_hex (Megan) earned 668 total points
ID: 26290062
if you don't want this problem to re-occur, you should change the Recovery Model on sharepoint databases to simple.  of course, you should be aware of the implications.. namely, you will lose the ability to recover sharepoint databases from sql log files (ummm... not sure why you'd want to do that anyway, if you're doing proper backups), and you'll lose the ability to recover sharepoint databases point in time...  which again, is a pretty rare requirement.  in many situations, recovery to the last backup is sufficient.

if that's your case, change the recovery model to simple, and then run shrink.  if you do not change the recovery model (and just do shrink, or just truncate), then your log file is going to get huge again.

so, in the majority of cases, i'd recommend changing the recovery model to simple and run shrink.  the exception is if you've got some kind of requirement to recovery point in time and recovery from the last backup is not sufficient.

Author Closing Comment

ID: 31675839
Switching my databases to Simple for Sharepoint seems to be the way to go. Also, by truncating and then shrinking my databases, they are back under control.

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

765 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