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

• Microsoft SQL Server versions 4.2x, 6.0, 6.5, 7.0

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.

Additional query words:

Keywords : kbother SSrvServer
Version : winnt:4.2x,6.0,6.5,7.0
Platform : winnt
Issue type :