The bulk copy program (bcp) accepts as one of its parameters:
/b batchsize
This 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. 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 nonlogged (or "fast") bcp is used, the data insertions are not logged in the transaction log. However, SQL Server still logs 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 extent allocation logging before the copy is complete. In this situation, you can either enlarge the transaction log or perform the bcp copy by using the /b option and setting the database option Truncate Log on Checkpoint. 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 you use nonlogged bcp to import data into a SQL Server database, it is important to back up the database (by using the DUMP DATABASE command) when the bcp copy is complete.