Microsoft KB Archive/66057

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

Article ID: 66057

Article Last Modified on 2/14/2005

-

APPLIES TO


 * Microsoft SQL Server 4.21a Standard Edition
 * Microsoft SQL Server 6.0 Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q66057



SYMPTOMS
The Microsoft SQL Server &quot;System Administrator's Guide&quot; 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.

Keywords: kbother KB66057

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.