Prerequisites for Minimal Logging in Bulk Import

Prerequisites for Minimal Logging in Bulk Import

 

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2016)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

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, under the simple recovery model or bulk-logged recovery model, minimal logging of bulk-import operations reduces the possibility that a bulk-import operation will fill the log space. Minimal logging is also more efficient than full logging.

System_CAPS_ICON_note.jpg Note


The bulk-logged recovery model is designed to temporarily replace the full recovery model during large bulk operations.

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

  • The table is not being replicated.

  • Table locking is specified (using TABLOCK). For table with clustered columnstore index, you don't need TABLOCK for minimal logging. Additionally, only the data load into compressed rowgroups are minimally logged requiring a batchsize of 102400 or higher.

    System_CAPS_ICON_note.jpg 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.

  • Table is not a memory-optimized 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.

      System_CAPS_ICON_note.jpg 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 btree based clustered index and is non-empty, data pages and index pages are both fully logged regardless of the recovery model. For tables with clustered columnstore index, the dataload into compressed rowgroup is always minimally logged independent of the table being empty or not when batchsize >= 102400.

    System_CAPS_ICON_note.jpg Note


    If you start with an empty table rowstore 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.

System_CAPS_ICON_note.jpg Note


When transactional replication is enabled, BULK INSERT operations are fully logged even under the Bulk Logged recovery model.

Arrow icon used with Back to Top link [Top]

Recovery Models (SQL Server)
bcp Utility
BULK INSERT (Transact-SQL)
OPENROWSET (Transact-SQL)
BACKUP (Transact-SQL)
ALTER DATABASE (Transact-SQL)
Table Hints (Transact-SQL)
INSERT (Transact-SQL)

Community Additions

ADD
Show:
© 2016 Microsoft