SQL Server Performance Forum – Threads Archive

Large Log Files

Every night, we download data from our mainframe and load it into nto SQL2000 tables using BULK INSERT. The data in the tables is not updated during the day; the only updates are batch loads at night. The problem I am running into is that the log files grow very large. Each night, I backup the log with the TRUNCATE_ONLY option, but this does not seem to help. I tried using the BATCHSIZE parm for BULK INSERT, but I need to use @@ROWCOUNT to get the number of records loaded to each table. Is there a way to force no logging when doing these updates? Or is there a way to get the total number of records loaded when using them BATCHSIZE parm?

---

Which recovery mode are you using? full, simple, or bulk-logged? The bulk-logged option uses the least amount of log space under these conditions. Logging cannot be turned off, but the bulk-logged option uses the least log space. When you truncate a log file, it doesn’t reduce the physical size of the log, only the contents of the log. If you want to reduce the physical size of the log file, you will have to shrink it. Shrinking a log file is not always easy. See the tip at the bottom of this webpage about shrinking log files: http://www.sql-server-performance.com/misc_tips.asp
——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com

---

FOr such bulk mode operations BULK-LOGGED recovery is recommended, make sure to finish Tlog backup before and after the operation. Check the reference provided by Brad which works perfectly. HTH Satya SKJ

---

Thanks for the help. I was using simple recovery.

---

]]>