[dba-SQLServer] Runaway Log File Questions

Hi!
Well for starters, SQL Server is a hog and will take whatever you let it.
The logs must be truncated on a regular basis, depending on how much data
you need and if you need to be able to restore to a point in time.
We don't need the logs so whenever we load data we immediately truncate the
logs.  We back up every night and our data is in loader files that we also
keep for disaster recovery.
We do to things to keep our logs in check:

This is run after a long running query that blows our logs way up.  Our logs
are set to 100 megs and when we shrink them we use 100MB to specify how far
down to shrink them or else they'll go back to the default of growing
unlimited.

	DUMP TRAN tempdb with no_log

     -- Shrink Log Files
     DBCC SHRINKFILE(dbName_log, 100)

You can also use the dump tran script to empty out a log that is way too
big, your best bet would be to start there.
Check out BOL under BACKUP and look for the section on transaction logs.

Carolina

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of
chizotz at mchsi.com
Sent: Thursday, March 03, 2005 10:35 AM
To: dba-SQLServer at databaseadvisors.com
Subject: [dba-SQLServer] Runaway Log File Questions


I have several questions that I'm trying to answer, and any assistance is
appreciated.

I have a SQL Server 2000 database that has a data file of 180 meg and a log
file of over 72 gig. The db was set up basically using all of the defaults,
and it is aparently logging every transaction. That degree of logging is not
really necessary, at least I don't believe it is and almost certainly not at
the moment when I'm essentially the only developer/user of the db.

So my questions are:

1) What is the correct way to delete that huge log file and start the log
over?
2) What is the correct way to limit the size of the log file? If I set the
max
log file size to, say, 10 gig, what happens when the log reaches 10 gig?
Does
it delete the oldest records and keep logging new stuff, or does it just
choke?
3) What is the correct way to estimate what max size the log file "should"
be?
4) I've seen mention of "filtering" development activity out of the log so
it
doesn't get clogged up with uneeded junk, but so far I see no explanation of
how this is accomplished.

I've looked at the BOL but either I haven't found the correct topic yet or
I'm
not "getting" it yet. I also have "SQL Server Fast Answers for DBAs and
Developers" by Joseph Sack, but I'm either missing the correct section or
not
understanding it yet. I think this is a good book, but perhaps assumes a
level
of experience that I don't quite have yet.

As you can tell, I'm relatively inexperienced with SQL Server
administration,
but I'm learning. I'm going to go tonight and see what other books are
available and try to get a good reference work to have handy. Any
suggestions
on a specific book or two to look for?

Thank you,

Ron Allen
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com