PRB: Running Out of Log Space When Running Large Bulk Loads

ID Number: Q66057

1.00 1.10 1.11 4.20

OS/2

Summary:

SYMPTOMS

Page 133 of the "Microsoft SQL Server System Administrator's Guide"

states that when using the fast version of BCP (no indexes on the

table and the sp_dboption SELECT INTO/BULKCOPY set to true), the

data inserts are not logged in the transaction log. However, when

running large bulk loads, you may still run out of log space.

CAUSE

When SQL Server performs a nonlogged operation, such as using the

fast version of BCP or performing a SELECT INTO on a permanent

table, the actual data insertions are not written to the

transaction log. However, as new data pages and extents are

allocated, they are recorded in the log. This information is

necessary in that it allows the server to track used data

structures.

More Information:

Generally, the amount of overhead written to the transaction log

during a nonlogged operation is nominal. However, if your transaction

log is almost full, issuing a nonlogged operation could cause the log

to run out of space. To complete the operation, you must either

truncate the transaction log or increase its size.