[dba-SQLServer] Help: What to do when transaction log file is full

Ping Li pli at umn.edu
Wed Oct 5 09:19:02 CDT 2005


Hi,

I am absolutely new to DBA tasks and I really need help from you all.

Last week, after I used DTS wizzard to copy a huge table as a backup 
copy, I used query analyzer to truncate the old table and insert new 
records into it.  It ran for a long time and came back with an error 
message saying "the log file is full, free up some space...".  I checked 
the database.  It was set to use simple recovery model and with a static 
size.  The database is set to backup complete with no log option.  The 
reason for what is that the database is more of a data mart than 
transactional database.

The settings are as follows:
Data file: space allocated: 100000MB.
Log file: space allocated: 3000MB; automatically growth--checked; file 
growth: 1000 in MB; Maximum size: restricted file growth(MB) 3100

I talked with somebody.  Some suggested me change the database to grow 
automatically or auto-increment by 10% while the other strongly urge not 
to set it to grow automatically.  One of people I talked shrink the log 
regularly.

I would appreciate any imput on what's the best practice in managing log 
files.  Thank you,

Ping Li



More information about the dba-SQLServer mailing list