Bulk Copies into a Database

You can also break up large transactions when using bcp to bulk copy data into a database. If you use bcp without specifying a batch size, the entire operation is performed as a single logical transaction.

Even if another user process does a DUMP TRANSACTION statement, the log records associated with the bulk copy operation remain in the log until the entire operation completes or another DUMP TRANSACTION statement is executed. This is one of the most common causes of the 1105 error. You can avoid the problem by splitting the bulk copy operation into batches.

To ensure recoverability, follow this procedure:

Set the trunc. log on chkpt. option to true:
```
use master
go
sp_dboption database_name, trunc, true
go
```
Note The trunc option is an abbreviated form of the trunc. log on chkpt. option. The two options are interchangeable.
Run bcp from the command line using the /b batchsize option, as shown in the following example:
```
bcp database_name..table_name in /b 100 /U sa /P sa_password 
/S server_name
```
When the bcp operations are complete, turn off the trunc. log on chkpt. option and dump the database.

In this example, a batch size of 100 rows is specified. This results in one transaction for every 100 rows copied. In some cases, you might also need to break the bcp input file into two or more separate files and execute a DUMP TRANSACTION after copying each file (to prevent the transaction log from filling up).

If bcp is performed in the fast mode, the operation is not logged. In other words, only the space allocations are logged, not the complete table, so the transaction log cannot be dumped to a device. In this case, you must use the DUMP TRANSACTION WITH TRUNCATE_ONLY statement to free space in the log. Remember, however, that after you execute DUMP TRANSACTION WITH TRUNCATE_ONLY, you must dump the database before you can dump the transaction log to a device.

For additional information about using bcp, see the Microsoft SQL Server Transact-SQL Reference.