SQL Server - Minimally logged operations in SQL Server

When the database is in bulk-logged or simple recovery model than not all the operations are logged only the information that is required to recover the transaction without supporting point-in-time recovery. This is also called minimal logging. To recover the data up to a point-in-time then the database must be in full recovery model.

Here I am going to list out the few operations which are fully logged in full recovery model and are minimally logged to transaction log in simple and bulk-logged recovery model:

1.       Bulk import operations (minimally logged only when the below conditions are met)

·        The table is not being replicated.

·        Table locking is specified (using TABLOCK).

·        If the table has no indexes, data pages are minimally logged.

·        If the table has one or more nonclustered indexes (no clustered index) and the table is empty, both data and index pages are minimally logged. If the table has one or more nonclustered indexes (no clustered index) and the table is not empty, then only index pages are fully logged.

·        If the table has a clustered index and is empty, both data and index pages are minimally logged. If the table has a clustered index and is not empty, data and index pages are fully logged.

2.       SELECT…INTO is a minimally logged operation, this can be more efficient rather than creating a table and then populating the table with an INSERT statement.

3.       TRUNCATE TABLE statement is minimally logged, only the page deallocations are logged in transaction log.

4.       Partial updates to large value data types, using the .WRITE clause in UPDATE statement when inserting or appending new data. Minimal logging is not used when existing values are updated.

5.       WRITETEXT and UPDATETEXT statements (deprecated and will be removed in future SQL Server versions) when inserting or appending new data into text, ntext and image data type columns. Minimal logging is not used when existing values are updated.

6.       CREATE INDEX operation (including indexed views).

7.       ALTER INDEX REBUILD.

8.       DROP INDEX new heap rebuild. Index page de-allocation during a DROP INDEX operation is always fully logged.

If you like this post, do like us on Facebook at http://www.facebook.com/SQLServerGeeks

Regards,
Ravi.