[dba-SQLServer] Runaway Log File Questions

chizotz at mchsi.com chizotz at mchsi.com
Thu Mar 3 11:34:32 CST 2005


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



More information about the dba-SQLServer mailing list