Prerequisites for Minimal Logging in Bulk ImportPrerequisites for Minimal Logging in Bulk Import

适用对象: yesSQL ServeryesAzure SQL 数据库noAzure SQL 数据仓库no并行数据仓库APPLIES TO: yesSQL Server noAzure SQL Database noAzure 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.

备注

大容量日志恢复模式旨在于大容量操作期间临时替换完整的恢复模式。The bulk-logged recovery model is designed to temporarily replace the full recovery model during large bulk operations.

在大容量导入操作中最小日志记录的表要求Table Requirements for Minimally Logging Bulk-Import Operations

最小日志记录要求目标表满足下列条件:Minimal logging requires that the target table meets the following conditions:

  • 当前没有复制表。The table is not being replicated.

  • 指定了表锁定(使用 TABLOCK)。Table locking is specified (using TABLOCK).

    备注

    尽管在最小日志记录的大容量导入操作过程中,数据插入操作没有记录在事务日志中,但每当为表分配新区时,数据库引擎Database Engine仍会记录区分配信息。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 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 table is non-empty, index pages are fully logged.

  • 如果表有聚集索引且为空,则按最小方式记录数据页和索引页。If the table has a clustered index and is empty, both data and index pages are minimally logged. 相反,如果表有基于 btree 的聚集索引且不为空,则无论采用何种恢复模式,均按完整方式记录数据页和索引页。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. 如果以空表和行存储表开始并批量导入数据,对于第一批,将按最小方式记录索引页和数据页,但从第二批开始,将只按批量方式记录数据页。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.

  • 有关记录群集列存储索引 (CCI) 的信息,请参阅列存储索引数据加载指南For information about logging for a clustered columnstore index (CCI), see Columnstore index data loading guidance.

备注

启用事务复制时,将完全记录 BULK INSERT 操作,即使处于大容量日志恢复模式下。When transactional replication is enabled, BULK INSERT operations are fully logged even under the Bulk Logged recovery model.

相关任务Related Tasks

另请参阅See Also

恢复模式 (SQL Server) Recovery Models (SQL Server)
bcp 实用工具 bcp Utility
BULK INSERT (Transact-SQL) BULK INSERT (Transact-SQL)
OPENROWSET (Transact-SQL) OPENROWSET (Transact-SQL)
BACKUP (Transact-SQL) BACKUP (Transact-SQL)
ALTER DATABASE (Transact-SQL) ALTER DATABASE (Transact-SQL)
表提示 (Transact-SQL) Table Hints (Transact-SQL)
INSERT (Transact-SQL)INSERT (Transact-SQL)