[RESOLVED] SQLServer 2000, DTS, Log Files

[DKenny]

Is there a way to disable writing to the Log file while a DTS package is running?

I'm trying to run a load from text file of approx 4.5M records to a single table and it keeps erroring out, saying that the transaction log is full and needs to be backed up.


Thanks in advance

[brucevde]

I sure hope the answer is No.

Which Recovery Model are you using? What does the DTS package do? You can switch between Full and Bulk Logged recovery modes. What about backing up the transaction log after x amount of transactions.

Personally, I would just allow the Transaction Log to automatically grow as needed.

[DKenny]

Bruce
In this case I definetly don't need a trans-log, its a bulk load of data (4GB) for some once-off analysis.

I've changed the recovery mode to Bulk Logged, hopefull ythat will do it.

What about backing up the transaction log after x amount of transactions.

I'm using a BCP task in a DTS package to do the load, so not sure this option is possible.
How else could I do in step with a BACKUP LOG...TRUNCATE_ONLY between the steps?

[brucevde]

What options are you using with BCP? I found this in BOL

When bulk copying large data files into an instance of SQL Server, it is possible for the transaction log to fill before the bulk copy is complete, even if the row inserts are not logged, from the extent allocation logging. In this situation, enlarge the transaction log, allow it to grow automatically or perform the bulk copy using the -b or BATCHSIZE switch, and set the recovery model to simple. Because only committed transactions can be truncated, this option does not free up space during the bulk copy operation if the -b switch is not used; the entire operation is logged as a single transaction

.

[brucevde]

I also found this.

Logged and Nonlogged Bulk Copies
Microsoft® SQL Server™ bulk copies that import data into an instance of SQL Server are run in either logged or nonlogged mode. The difference between logged and nonlogged bulk copy operations is how much information is logged. Both logged and nonlogged bulk copy operations can be rolled back, but only a logged bulk copy operation can be rolled forward.

In a logged bulk copy all row insertions are logged, which can generate many log records in a large bulk copy operation. These log records can be used to both roll forward and roll back the logged bulk copy operation. In a nonlogged bulk copy, only the allocations of new pages to hold the bulk copied rows are logged. This significantly reduces the amount of logging that is needed and speeds the bulk copy operation. If a nonlogged bulk copy operation encounters an error and has to be rolled back, the allocation log records are used to deallocate the pages holding the bulk copied rows. Since the individual row insertions are not logged in a nonlogged bulk copy, however, there is no log record of the individual rows that could be used to roll forward nonlogged bulk copy operations. This is why a nonlogged bulk copy operation invalidates a log backup sequence.

If the database option trunc. log on chkpt. is set on, then there is no need to generate log records that would support rolling forward a bulk copy operation. Use nonlogged bulk copy operations in databases where trunc. log on chkpt. is turned on.

Whether a bulk copy is logged or nonlogged is not specified as part of the bulk copy operation; it is dependent on the state of the database and the table involved in the bulk copy. A nonlogged bulk copy occurs if all the following conditions are met:

• The database option select into/bulkcopy is set to true.
  
• The target table has no indexes, or if the table has indexes, it is empty when the bulk copy starts.
  
• The target table is not being replicated.
  
• The TABLOCK hint is specified using bcp_control with eOption set to BCPHINTS.

Any bulk copy into SQL Server that does not meet these conditions is logged.

[DKenny]

Bulk Logged did the trick.

Thanks for the help