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

• The table is not being replicated.
  
• Table locking is specified (using TABLOCK).
  For more information, see Controlling Locking Behavior for Bulk Import.
  

  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.

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

• If the table has no indexes, data pages are minimally logged.
  
• If the table has no clustered index but has one or more nonclustered indexes, data pages are always minimally logged. How index pages are logged, however, depends on whether the table is empty:
  
  • 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, both index and data pages are minimally logged for the first batch, 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 non-empty, 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 batches, both index and data pages are minimally logged for the first batch, 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 for backup. The next log backup will reclaim the log space that is used to bulk import that batch of rows.

Concepts

Other Resources

Help and Information