sql server new users: LDF File Size, How big is too big?? and -- sql server new users

You're in the

sql server new users

group:

LDF File Size, How big is too big?? and

WANNABE
9/26/2006 9:12:07 AM

sql server new users:

What is the best way to manage Log file growth?? We have a document
management program and the MDF file is about 3.5 gig, but the LDF file
suddenly ballooned up to 34 gig. The data is backed up nightly and the logs
are backed up hourly. I thought that would allow the log file to "flush
out", obviously I was wrong. The file is set to full recovery Model with
torn page detection, (SQL2000) and when I look at the percentage of unused
space on that file there's only about 15% . Can someone tell me why this
grows this big and what I can do to keep in under control??? I am running
low on HD space... Thanks...

Re: LDF File Size, How big is too big?? and

Andrew J. Kelly
9/26/2006 10:07:36 PM

Most likely you have a long running open transaction that is preventing the
log from being truncated. What does DBCC OPENTRAN() say for that db?

--
Andrew J. Kelly SQL MVP

[quoted text, click to view]

Re: LDF File Size, How big is too big?? and

WANNABE
9/27/2006 11:24:19 AM

Thanks Andrew, this is what I get today from DBCC OPENTRAN on that DB >"No
active open transactions." BUT, I modified the configuration to SIMPLE and
cleared out the log, it is now 1024k. I'm sure there is a better way of
handling this, and I would appreciate any recommendations. If I returned it
to FULL, what other settings should I put in place to ensure that the log
file does not become too large?? There are 2 statistics settings on the
Options tab can you also explain those and recommend best performance
settings.. Thank you !!
========================================
[quoted text, click to view]

Re: LDF File Size, How big is too big?? and

Andrew J. Kelly
9/27/2006 10:19:49 PM

I can't say why it wasn't working as expected if there were no open trans. I
would keep an eye on it to see if it grows again. Once you put it back in
FULL recovery mode you need to take a FULL backup before the log backups
will be of use and truncate as expected. The statistics settings are best
kept at the Auto setting by default. Some apps can perform better with one
or more off but unless you know of a specific reason to turn them off you
should keep them at the default.

--
Andrew J. Kelly SQL MVP

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server new users group.

home · blog · groups

Questions? Comments? Contact the dn