ID Number: Q81339
1.10 1.11 4.20
OS/2
Summary:
The bulk copy program (BCP) accepts "/b <batch size>" (without
quotation marks) as one of its parameters. This "/b <batch size>"
parameter allows the user to specify the number of rows per batch for
the data that will be loaded into a SQL Server table. The use of the
/b parameter has a large impact on how the data insertions are logged.
More Information:
When the /b parameter is not used with the BCP utility, all rows from
a data file are copied into SQL Server in one batch, and BCP displays
the message "1000 rows sent to SQL Server" after every 1000 rows have
been copied.
When the non-logged (or "fast") version of BCP is used, the data
insertions are not logged in the transaction log. However, SQL Server
still logs page allocations each time a new data page is allocated, as
well as extent allocations each time a new extent is allocated to the
table. (An extent is a block of eight 2K data pages.)
If the /b parameter is not used, SQL Server treats the entire BCP
operation as a single transaction. Thus, if for any reason the BCP is
aborted before it finishes, the entire transaction is rolled back, and
the destination table will not contain any new rows from the BCP
operation.
If the /b parameter is used, each batch of rows is logged as a
separate transaction. For example, if a data file has 1000 rows, and a
batch size of 100 is used, SQL Server will log the operation as 10
separate transactions. If the BCP operation were to abort while
copying in row 750, only the previous 49 rows would be removed, and
the destination table would still contain the first 700 rows.
When copying large data files into SQL Server with BCP, it is possible
for the transaction log to fill up from the page and extent allocation
logging before the BCP is complete. In this situation, you can either
enlarge the transaction log or do the BCP by using the /b option (with
the "trunc. log on chkpt." database option enabled). Setting this
option "on" will instruct SQL Server to truncate the log each time it
performs a CHECKPOINT, so the log records for those batches that have
been committed will be removed from the log. Because only committed
transactions can be truncated from the log, this option will not free
up space in the log if the /b option is not used (because the entire
operation is logged as a single transaction).
Note: When using the non-logged version of BCP to import data into a
SQL Server database, it is important to back up the database (using
the DUMP DATABASE command) when the BCP is complete.