PRB: Running Out of Log Space When Running Large Bulk Loads (66057)
The information in this article applies to:
- Microsoft SQL Server 4.2x
- Microsoft SQL Server 6.0
- Microsoft SQL Server 6.5
- Microsoft SQL Server 7.0
This article was previously published under Q66057 SYMPTOMS
The Microsoft SQL Server "System Administrator's Guide" and Books Online state 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.
| Modification Type: | Minor | Last Reviewed: | 2/14/2005 |
|---|
| Keywords: | kbother KB66057 |
|---|
|