Logged and Nonlogged Bulk Copy Operations

By default, all row-insert operations performed by bcp are logged in the transaction log. For large data loads, this can cause the transaction log to fill rapidly. To help prevent the transaction log from running out of space, a nonlogged bulk copy can be performed if all of these conditions are met:

• The database option select into/bulkcopy is set to true using sp_dboption.
• The target table is not being replicated.
• The TABLOCK hint is specified. For more information, see Controlling the Locking Behavior.

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.

Any bulk copy into Microsoft® SQL Server™ that does not meet these conditions is logged.

Important Before a user can do nonlogged bulk copies, the system administrator or database owner must first use SQL Server Enterprise Manager (or the sp_dboption system stored procedure) to set the select into/bulkcopy option for that database to true. If the option is not set and a user tries to bulk copy data into a table that does not have indexes, SQL Server generates a warning message and logs the bulk copies. By default, select into/bulkcopy is false in newly created databases. To change the default for all new databases, set this option in the model database.

After performing a nonlogged bulk copy, it is no longer possible to back up the transaction log. Therefore, it is recommended that a database or differential database backup is created instead. For more information, see Backing Up Nonlogged Operations.

While minimal logging occurs when bulk copying data into a table with indexes, the transaction log can still become full. The log can be truncated using the Transact-SQL statement BACKUP LOG specifying the TRUNCATE_ONLY clause.

When bulk copying a large number of rows into a table with indexes, it can be faster to drop all the indexes, perform the bulk copy, and re-create the indexes. For more information, see Optimizing Bulk Copy Performance.

Note Although data insertions are not logged in the transaction log when a nonlogged bulk copy is performed, SQL Server still logs extent allocations each time a new extent is allocated to the table.

See Also