Share via

  • Article

Operations That Can Be Minimally Logged

Minimal logging involves logging only the information that is required to recover the transaction without supporting point-in-time recovery. This topic identifies the operations that are minimally logged under the bulk-logged recovery model (as well as under the simple recovery model, except when a backup is running).

Note

Under the full recovery model, all bulk operations are fully logged. However, you can minimize logging for a set of bulk operations by switching the database to the bulk-logged recovery model temporarily for bulk operations. Minimal logging is more efficient than full logging, and it reduces the possibility of a large-scale bulk operation filling the available transaction log space during a bulk transaction. However, if the database is damaged or lost when minimal logging is in effect, you cannot recover the database to the point of failure.

The following operations, which are fully logged under the full recovery model, are minimally logged under the simple and bulk-logged recovery model:

  • Bulk import operations (bcp, BULK INSERT, and INSERT... SELECT). For more information about when bulk import into a table is minimally logged, see Prerequisites for Minimal Logging in Bulk Import.

    Note

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

  • SELECT INTO operations. For more information, see INTO Clause (Transact-SQL).

    Note

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

  • Partial updates to large value data types, using the .WRITE clause in the UPDATE statement when inserting or appending new data. Note that minimal logging is not used when existing values are updated. For more information about large value data types, see Using Large-Value Data Types. For more information about the .WRITE clause, see UPDATE (Transact-SQL).

  • WRITETEXT and UPDATETEXT statements when inserting or appending new data into the text, ntext, and image data type columns. Note that minimal logging is not used when existing values are updated. For more information, see WRITETEXT (Transact-SQL) and UPDATETEXT (Transact-SQL).

    Note

    The WRITETEXT and UPDATETEXT statements are deprecated, so you should avoid using them in new applications.

  • If the database is set to the simple or bulk-logged recovery model, some index DDL operations are minimally logged whether the operation is executed offline or online. The minimally logged index operations are as follows:

    • CREATE INDEX operations (including indexed views).

      For more information, see CREATE INDEX (Transact-SQL).

    • ALTER INDEX REBUILD or DBCC DBREINDEX operations.

      For more information, see ALTER INDEX (Transact-SQL).

      Note

      The DBCC DBREINDEX statement is deprecated so you should avoid using it in new applications.

    • DROP INDEX new heap rebuild (if applicable).

      Note

      Index page deallocation during a DROP INDEX operation is always fully logged.

      For more information, see DROP INDEX (Transact-SQL).

For more information about how the recovery model affects logging of index operations, see Choosing a Recovery Model for Index Operations.