Microsoft KB Archive/295724

= PRB: Nonlogged BCP Operation Can Be Logged on Tables with Indexes and Existing Data =

Article ID: 295724

Article Last Modified on 4/22/2001

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q295724



SYMPTOMS
The &quot;Logged and Nonlogged Bulk Copy Operations&quot; topic in SQL Server Books Online states the following regarding the logging of bulk copy operations:

&quot;Additionally, if the table has any indexes, then there should be no existing rows in the table to perform a nonlogged bulk copy. The combination of indexes and existing rows of data cause the bulk copy operation to be logged.&quot;

That statement is incorrect. If the following conditions are true:
 * The Select into/bulk copy option is set to TRUE.
 * The table has rows.
 * The table has an index.

the bulk copy operation is still fully logged; each inserted record is logged. However, a nonlogged transaction log record is generated as part of this operation, which has two effects:
 * Transaction log backups cannot be made until either a full database backup or a differential backup is done.
 * More transaction log space is used than would be the case if the operation were nonlogged.



CAUSE
This behavior occurs because statistics are updated for the index in a nonlogged fashion. It does not matter whether the Auto-create statistics option is turned on or off.



WORKAROUND
Right-click the database, click Properties, click Options, and turn off the Select into/bulk copy option.

Keywords: kbprb KB295724

-

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

© Microsoft Corporation. All rights reserved.