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.
Note
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:
- 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).
Note
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).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
| Release | History |
|---|---|
5 December 2005 |
|