INSERT statement with TABLOCK hint can be minimum logged under full recovery mode in SQL Server 2014, why?

Question

From Microsoft Understanding Minimally Logged Operations

Minimally logged operations are available only if your database is in bulk-logged or simple recovery mode.

The code below explains what I saw.

use master;

IF DB_ID('RecoveryETL') IS NOT NULL
BEGIN
    ALTER DATABASE RecoveryETL SET SINGLE_USER WITH ROLLBACK IMMEDIATE 
    DROP DATABASE RecoveryETL;
END

--Create sample database for testing
CREATE DATABASE RecoveryETL 
ON PRIMARY
    (NAME = N'RecoveryETL_Date', FILENAME = N'C:\SQLDATA\RecoveryETL_Data.Mdf', SIZE = 1024MB, MAXSIZE = UNLIMITED, FILEGROWTH = 50MB)
LOG ON
    (NAME = N'RecoveryETL_Log', FILENAME = N'C:\SQLDATA\RecoveryETL_Log.LDF', SIZE = 100MB, MAXSIZE = 1024MB, FILEGROWTH = 25MB)
GO

--Set Recovery Mode to Full
ALTER DATABASE RecoveryETL SET RECOVERY FULL ;
GO

USE RecoveryETL;
GO

--Immediately perform a full backup otherwise database will remain in simple mode
BACKUP DATABASE RecoveryETL
TO DISK = 'C:\Database Backups\RecoveryETL_1.bak'
WITH RETAINDAYS = 90 
, FORMAT
, INIT
, MEDIANAME = 'RecoveryETL'
, NAME = 'RecoveryETL-Full Database Backup'
, COMPRESSION ;
GO

--Create a Testing Table
IF OBJECT_ID('dbo.HeapTable') IS NOT NULL 
DROP TABLE dbo.HeapTable

CREATE TABLE HeapTable
(
    Fld1 INT, 
    Fld2 INT, 
    FId3 INT,
    ModDate Datetime
)

--Insert Records W/O TABLOCK hint
INSERT INTO HeapTable (Fld1, Fld2, FId3, ModDate)
    SELECT TOP 10000 
        SalesOrderDetailID, SalesOrderDetailID + 100, SalesOrderDetailID + 99, GETDATE()
    FROM 
        AdventureWorks2012.Sales.SalesOrderDetail

--Is it minimum logged? No, more than 10,000 log records
SELECT * 
FROM sys.fn_dblog(null, null)
WHERE AllocUnitName LIKE '%HeapTable%' 

--Truncate logs
CHECKPOINT;

BACKUP LOG RecoveryETL
TO DISK = 'NULL'
WITH RETAINDAYS = 90
, NOINIT
, MEDIANAME = 'RecoveryETL'
, NAME = 'RecoveryETL-Log Backup'
, COMPRESSION ;
GO

--Make sure logs are gone
SELECT * 
FROM sys.fn_dblog(null, null)
WHERE AllocUnitName LIKE '%HeapTable%' 

--Insert records WITH TABLOCK hint
INSERT INTO HeapTable WITH(TABLOCK)(Fld1, Fld2, FId3, ModDate)
    SELECT TOP 10000 
        SalesOrderDetailID, SalesOrderDetailID + 100, SalesOrderDetailID + 99, GETDATE()
    FROM 
        AdventureWorks2012.Sales.SalesOrderDetail

--Minimum logged here!!!!!
SELECT * 
FROM sys.fn_dblog(null, null)
WHERE AllocUnitName LIKE '%HeapTable%' 

I was also trying to restore database point-in-time using the log backup with minimum logged logs, as expected I was not able to bring database out of restoring state, where I can say they are truly minimum logged. The official documentation is inaccurate?

Answer 1

where I can say they are truly minimum logged. The official documentation is inaccurate?

No, you are wrong.

Yes there were changes in how INSERTs are fully logged, but under full recovery model even with TABLOCK they are still fully logged.

In SQL SERVER 2005-2008 INSERT under full recovery model was logged row by row, so if you made 10000 rows INSERT in the log file you saw at least 10000 LOP_INSERT_ROW operations (see my first pictur)

Now I made the same INSERT on SQL SERVER 2016 first under BULK_LOGGED recovery model and then under the FULL (you can see the result on my second picture). Under BULK_LOGGED you can see that only bitmap settings were logged, so operation is really minimally logged. but under FULL recovery moder the picture is different. Yes you don't see 10000 LOP_INSERT_ROW rows anymore but now you can see 38 LOP_FORMAT_PAGE operations, eand very log row corresponding to this operetion is of 8276 bytes, i.e. 8Kb, one page. These 38 pages are full page images that were formatted during the INSERT, as it was done even in 2008 for INDEX REBUILD operations. So the operation is still fully logged, the info that goes into the log is enough to completely reconstitute operation, not only to permit the rollback as in case of minimally logged operations. So INSERT into a heap even with TABLOCK hint is still fully logged operation but now it's even efficiently logged operation. I tried even to backup log after my INSERT under the FULL and I was able to restore from this log with stop at at the time prior to backup log was finished.