Prerequisites for Minimal Logging in Bulk Import

  • Article

A bulk import operation can be minimally logged only under the bulk-logged recovery model or simple recovery model. For a database under the full recovery model, all row-insert operations that are performed by bulk import are fully logged in the transaction log. Large data imports can cause the transaction log to fill rapidly if the full recovery model is used. In contrast, minimal logging reduces the possibility that a bulk-import operation will fill the log space. Minimal logging is also more efficient than full logging. Minimal logging requires that the database is using either the bulk-logged recovery model (if the database normally uses full recovery) or the simple recovery model.

Note

For information on switching between the full recovery model and bulk-logged recovery model, see Considerations for Switching from the Full or Bulk-Logged Recovery Model.

Table Requirements for Minimal Logging

Minimal logging requires that the target table meets all of the following conditions:

Note

Although data insertions are not logged in the transaction log during a minimally logged bulk-import operation, the Database Engine still logs extent allocations each time a new extent is allocated to the table.

Indexes in Tables

Whether minimal logging can occur for a table also depends on whether the table is indexed and, if so, whether the table is empty, as follows:

  • If the table has no indexes, data pages are minimally logged.

  • If the table has no clustered index but has one or more non-clustered indexes, data pages are always minimally logged. How index pages are logged, however, depends on whether the table is empty, as follows:

    • If the table is empty, index pages are minimally logged.
    • If table is non-empty, index pages are fully logged.

    Note

    If you start with an empty table and bulk import the data in multiple batches, for the first batch both index and data pages are minimally logged, but beginning with the second batch, only data pages are minimally logged.

  • If the table has a clustered index and is empty, both data and index pages are minimally logged.
    In contrast, if a table has a clustered index and is nonempty, data pages and index pages are both fully logged regardless of the recovery model.

    Note

    If you start with an empty table and bulk import the data in multiple batches, for the first batch both index and data pages are minimally logged, but from the second batch onwards, only data pages are bulk logged.

For more information, including a summary of table locking and logging behavior during bulk import, see Optimizing Bulk Import Performance.

Best Practice   When you use bulk-logged recovery to import a large set of table rows, consider distributing bulk imports among multiple batches. Each batch equates to one transaction. Thus, when a batch completes, its log becomes available to be backed up. The next log backup will reclaim the log space that is used to bulk import that batch of rows.

See Also

Concepts

Choosing the Recovery Model for a Database

Other Resources

bcp Utility
BULK INSERT (Transact-SQL)
OPENROWSET (Transact-SQL)
BACKUP (Transact-SQL)
ALTER DATABASE (Transact-SQL)
SuspendIndexing Property
UseBulkCopyOption Property

Help and Information

Getting SQL Server 2005 Assistance