Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Sun Foster
Aged Yak Warrior
515 Posts |
 Posted - 2005-12-10 : 06:45:54
|
| We need to use truncate about 15 tables in local server and insert more than 5000000 records to each tablbe from remote server every night. This caused log file grew to fast.(much bigger than data file)How to use code to keep log file in normal size every day? |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2005-12-10 : 07:23:45
|
set this option. See Books OnLine for detailUSE masterEXEC sp_dboption 'your_database', 'trunc. log on chkpt.', 'TRUE' -----------------[KH]Where Am I ? |
 |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-12-10 : 13:15:58
|
| How are you inserting the data? My guess is that the inserts are causing your logs to grow. If this is a pure batch process, you might want to consider setting the database to bulk-logged or simple recovery mode, importing the data through a bulk process, then doing a full backup after your entire process is done each night and resetting the database to full recovery mode. Only do the above though if space is really an issue. If not, then don't shrink the transaction log file. Just plan on it staying at the max size it gets each night. Even if it's not used during the day, your processes at night will be quicker because you wont have to be incrementally growing the file through the entire process. MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
|
|
|
log file grow too big