Minimally Logged Operations

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.

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

• Bulk import operations (bcp, INSERT ... SELECT * FROM OPENROWSET(BULK...), and BULK INSERT).
  For more information about when a bulk import into a table is minimally logged, see Prerequisites for Minimal Logging in Bulk Import.
  For more information about the bulk-import operations, see bcp Utility, BULK INSERT (Transact-SQL), and OPENROWSET (Transact-SQL).
• text, ntext, and image operations using the WRITETEXT and UPDATETEXT statements when inserting or appending new data. Note that minimal logging is not used when existing values are updated.
  For more information, see WRITETEXT (Transact-SQL) and UPDATETEXT (Transact-SQL).

• 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).

    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.

See Also

Concepts

Backup Under the Bulk-Logged Recovery Model
Optimizing Bulk Import Performance
Backup Under the Simple Recovery Model
Overview of the Recovery Models
Restore Under the Bulk-Logged Recovery Model
Considerations for Switching from the Full or Bulk-Logged Recovery Model

Other Resources

Deprecated Database Engine Features in SQL Server 2005

Help and Information

Getting SQL Server 2005 Assistance

Change History