Export (0) Print
Expand All

Minimally Logged Operations

Updated: 5 December 2005

Minimal logging involves logging the minimal amount of information in the transaction log 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) but fully logged under the full recovery model.

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. Bulk-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 bulk logging is in effect, you cannot recover the database to the point of failure.

ms191244.note(en-US,SQL.90).gifNote:
Unless a backup is running, minimal logging is used under the simple recovery model.

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

ms191244.note(en-US,SQL.90).gifNote:
The WRITETEXT and UPDATETEXT statements are deprecated, so you should avoid using them in new applications.

  • SELECT INTO operations.
    For more information, see SELECT (Transact-SQL).
  • Partial updates to large value data types, using the UPDATE statement's .WRITE clauses 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).
  • If the database is set to the 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).
      ms191244.note(en-US,SQL.90).gifNote:
      The DBCC DBREINDEX statement is deprecated so you should avoid using it in new applications.

    • DROP INDEX new heap rebuild (if applicable).
      ms191244.note(en-US,SQL.90).gifNote:
      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.

Release History

5 December 2005

Changed content:
  • Moved topic from the "Backing Up and Restoring Databases" section.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft