PRB: Nonlogged BCP Operation Can Be Logged on Tables with Indexes and Existing Data (295724)
The information in this article applies to:
This article was previously published under Q295724 SYMPTOMS
The "Logged and Nonlogged Bulk Copy Operations" topic in SQL Server Books Online states the following regarding the logging of bulk copy operations:
"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."
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.
| Modification Type: | Major | Last Reviewed: | 4/22/2001 |
|---|
| Keywords: | kbprb KB295724 |
|---|
|