Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

LDF SQL files have grown too big on MOSS 2007 Databases

Posted on 2010-01-11
7
Medium Priority
?
1,048 Views
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?
0
Comment
Question by:daramooka
7 Comments
 
LVL 26

Expert Comment

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

Accepted Solution

by:
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)
0
 
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
http://support.microsoft.com/kb/272318/

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

http://support.microsoft.com/kb/873235
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:daramooka
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.
0
 
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.


http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_23417091.html
--

0
 
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.
0
 

Author Closing Comment

by:daramooka
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.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
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…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

578 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