INF: Using Batch Sizes with the BCP Utility

  - Microsoft SQL Server version 4.2 for OS/2
  - Microsoft SQL Server version 4.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.