[dba-SQLServer] Runaway Log File Questions

Ron,

The basic transaction log "dance" you have to do to maintain a small log file are the following.

1. Make sure that the log file setting limits file growth to a manageable level commensurate with your disk capacity.
2. Set up a schedule to backup the log file and then shrink it at certain intervals of the day. For a heavy traffic site you might want to run the schedule every 30 mins or an hour.
3. To get maximum performance ensure that the logs are being written to a separate disk from where the data gets written using a separate disk controller if at all possible.

Now to get from the 72G you have to a more manageable level you need to do the following:

1. Create a backup device
2. Backup the database (data and log files)
3. Shrink the database and log file - this operation may have to be performed several times
4. When the log file reaches the limit you want it at, set up steps 1 and 2 above.

Alternatively you can detach the database, delete the log file and then reattach it. You will of course lose the ability to restore the data from the log file.

Make a backup first before you do so. :)

Eric

-----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 9: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



                                                                                                                                                                      
                                                                                                                                                                                                             ----------------------------------------------------------------
The information contained in this e-mail message and any file, document, previous e-mail message and/or attachment transmitted 
herewith is confidential and may be legally privileged. It is intended solely for the private use of the addressee and must not be 
disclosed to or used by anyone other than the addressee. If you receive this transmission by error, please immediately notify the 
sender by reply e-mail and destroy the original transmission and its attachments without reading or saving it in any manner.  If you 
are not the intended recipient, or a person responsible for delivering it to the intended recipient, you are hereby notified that any 
disclosure, copying, distribution or use of any of the information contained in or attached to this transmission is STRICTLY 
PROHIBITED. E-mail transmission cannot be guaranteed to be secure or error free as information could be intercepted, corrupted, 
lost, destroyed, arrive late or incomplete, or contain viruses.
The sender therefore does not accept liability for any errors or omissions in the contents of this message, which arise as a result of 
email transmission. Users and employees of the e-mail system are expressly required not to make defamatory statements and not 
to infringe or authorize any infringement of copyright or any other legal right by email communications. Any such communication is 
contrary to company policy. The company will not accept any liability in respect of such communication.