Minimal logging involves logging only the information that is required to recover a transaction without supporting point-in-time recovery.Minimal logging is more efficient than full logging, and it reduces the possibility of bulk operations filling up transaction log. .To make high-volume data loading faster, SQL Server supports minimal logging. However, with minimal logging you cannot perform a point in time recovery for bulk operations.

In fully logged operations, every row change is logged to transaction log to support point in time recovery. Minimally logged operations log extent allocations and metadata changes only. Since much less information is logged, minimally logged operations are much faster. Also, the log file is much smaller since there will be fewer writes.

Minimally logged operations can only be done if the recovery mode is either bulk-logged or simple. The bulk-logged recovery model is usually used as an adjunct to the full recovery model. Under this recovery model, most bulk operations are only minimally logged. If you use the full recovery model, you can switch temporarily to the bulk-logged recovery model before you perform bulk operations that can be minimally logged. The bulk logged recovery model resembles the full recovery model, except that it minimally logs most bulk operations.

Operations that can be Minimally Logged

Only certain operations can be minimally logged. Here is a list of operations that are minimally logged under bulk logged or simple recovery models . Note that these are fully logged in the full recovery model:

• Bulk import operations ( BCP, BULK INSERT and INSERT..SELECT )
• SELECT INTO operations
• TRUNCATE
• Starting in Sql server 2008 , INSERT SELECT statement can also be handled with minimal logging.
• Partial updates to large value data types, using the .WRITE clause.
• CREATE INDEX,ALTER INDEX REBUILD
• DROP TABLE
• Partition Switch
• Merge. (If 610 Trace flag is enabled)

Minimally logged operations can be in transactions. Since extent allocations are tracked, it is possible to roll back a minimal logged operation.

Prerequisites for Minimal Logging :

There are some prerequisites for minimal logging

• Table should not be replicated
• TABLOCK should be used
• You can specify TABLOCK with the command or turn on ‘table lock on bulk load’ table option . 

Ex :

BULK INSERT … WITH (TABLOCK)
CREATE TABLE TargetTable(x …, y …, z …);<br>
INSERT INTO TargetTable WITH (TABLOCK) (x, y, z)<br>
SELECT x, y, z FROM SourceTable;

EXEC sp_tableoption  dbo.Addresses,'table lock on bulk load',1

• If target table is indexed , it must be empty. If table has indexes and has data then behaviour is different. Both Data page updates and index page updates get logged. Following chart shows how minimal logging differs based on indexes and data

Note that if the table has a clustered index and has data, the operation is fully logged.

Trace Flag 610

SQl Server 2008 introduces trace flag 610, which controls minimally logged inserts to indexed tables.  You can turn the trace flag either by adding it to startup parameters or enable it per session.

DBCC TRACEON(610);

With this flag we don’t have to use TABLOCK to do insertions into tables with indexes. Also, insertions into non empty indexed tables can be done with minimal logging, at least partially. For tables with data, insertions in pages with data are fully logged. Only rows inserted into new pages are partially logged.

The following chart summarizes minimal logging behavious with 610 trace flag on (Src : BOL )  :

 (1) If you are using the INSERT … SELECT method, the ORDER hint does not have to be specified, but the rows must be in the same order as the clustered index. If using BULK INSERT the order hint must be used.

(2) Concurrent loads only possible under certain conditions. See “Bulk Loading with the Indexes in Place”. Also, only rows written to newly allocated pages are minimally logged.

(3) Depending on the plan chosen by the optimizer, the nonclustered index on the table may either be fully- or minimally logged.

Demo

The following script shows the difference between full and minimal logging .

-- Set Recover model to BULK_LOGGED
ALTER DATABASE TSQL2012
 SET RECOVERY BULK_LOGGED;
IF OBJECT_ID('TarTable') IS NOT NULL 
 DROP TABLE TarTable;
IF OBJECT_ID('TarHeap') IS NOT NULL 
 DROP TABLE TarHeap;
IF OBJECT_ID('SrcHeap') IS NOT NULL 
 DROP TABLE SrcHeap;
CREATE TABLE TarHeap( col1 INT ,col2 CHAR(4000),col3 CHAR(1000) ) ;
CREATE TABLE SrcHeap (col1 INT ,col2 CHAR(4000),col3 CHAR(1000) ) ; 
CREATE TABLE TarTable (col1 INT PRIMARY KEY ,col2 CHAR(4000),col3 CHAR(1000) ); 
--Insert row into source table
WITH Nums (col)
AS 
(
 SELECT 1 col
 UNION ALL 
 SELECT col + 1 FROM Nums
 WHERE col+1 <= 10000
)
INSERT INTO SrcHeap(col1) 
 SELECT col FROM Nums 
 OPTION (MAXRECURSION 10000)
--Insert rows to Target Table with (TABLOCK) Minimally logged
INSERT INTO TarHeap WITH(TABLOCK)
 SELECT * FROM SrcHeap 
-- Check Log Entries
SELECT TOP 10 operation [MINIMALLY LOGGED OPERATION ],context, [log record fixed length], [log record length], AllocUnitId, AllocUnitName
 FROM fn_dblog(null, null)
 WHERE allocunitname='dbo.TarHeap'
 ORDER BY [Log Record Length] DESC;
--Note That Log Record length is small 
--Insert rows to Target Table without (TABLOCK) fully logged
INSERT INTO TarHeap 
 SELECT * FROM SrcHeap WITH(NOLOCK);
-- Check Log Entries
SELECT TOP 10 operation [FULLY LOGGED OPERATION],context, [log record fixed length], [log record length], AllocUnitId, AllocUnitName
 FROM fn_dblog(null, null)
 WHERE allocunitname='dbo.TarHeap'
 ORDER BY [Log Record Length] DESC;
--Note That Log Record length is big 
--Insert rows to Target Table with clustered index and trace flag off - fully logged
INSERT INTO TarTable 
 SELECT * FROM SrcHeap WITH(NOLOCK);
SELECT TOP 10 operation [FULLY LOGGED OPERATION - EMPTY TABLE WITH CLUST INDEX 610 FLAG OFF],context, [log record fixed length], [log record length], AllocUnitId, AllocUnitName
 FROM fn_dblog(null, null)
 WHERE allocunitname LIKE '%TarTable%'
 ORDER BY [Log Record Length] DESC;
--Note That Log Record length is big 
CHECKPOINT;
GO
DBCC TRACEON(610);
TRUNCATE TABLE TarTable;
GO
--Insert rows to Target Table with clustered index empty table and trace flag ON - Minimally logged
INSERT INTO TarTable WITH(TABLOCK)
 SELECT * FROM SrcHeap WITH(NOLOCK); 
SELECT TOP 10 operation [MINIMALLY LOGGED OPERATION - EMPTY TABLE WITH CLUST INDEX 610 FLAG ON],context, [log record fixed length], [log record length], AllocUnitId, AllocUnitName
 FROM fn_dblog(null, null)
 WHERE allocunitname LIKE '%TarTable%'
 ORDER BY [Log Record Length] DESC;
--Note That Log Record length is small
GO
-- Turn off trace flag
DBCC TRACEOFF(610);
-- Set recovery model back to full
ALTER DATABASE TSQL2012
 SET RECOVERY FULL;

As is evident in the image, log records are smaller when operations are minimally logged .

To summarize, minimal logging writes less data to transaction log,  thereby reduces I/O, increases speed and also saves log space.