Log file full while in simple recovery mode

[gday2u]

I am new to SQLServer so please be gentle

I have set up a DTS package in SQLServer200 to populate tables in a database. The recovery mode is simple yet
I am getting the message:

The log file for database 'bwtest' is full.

I was lead to believe that transaction logging did not
occur in simple recovery mode. Have I missed something here ?
If some sort of logging is occuring should I do periodic
"dump transaction with no_log" statements within the
DTS package to get around this.

 

[tlbroadbent]

Yes, you missed something. SQL Server must log transactions even in simple mode in order to perform a rollback in the event of an error. If you are loading large amounts of data via DTS, by defaul SQL will load all the data from each import file as a single batch. After, the batch is loaded, SQL Server will commit the transaction and remove the committed transaction from the log file. However, the log may grow quite large before the batch finishes. You may see the error log full message when SQL Server cannot grow the log file fast enough to keep up with the transaction activity.

The best way around the problems is to set the Batch Size in the bulk insert or transform data tasks of the DTS package. As noted, the default batch size of 0 means all rows will be treated as a single batch. Setting the batch size to a positive number will cause SQL Server to commit batches of the indicated size. I often set the batch size to 10K or 20K rows depending on the total amount of data and the rows size. Others like to use even a amller batch size such as 1000 rows.

One additional benefit of setting the batch size is the improved performance of data loads. SQL Server doesn't have to grow the log so the process runs much faster.

See SQL BOL for details of setting the batch size.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm