I'm testing minimal logging inserts in different scenarios and from what I've read `INSERT INTO SELECT` into a heap with a non clustered index using `TABLOCK` and SQL Server 2016+ should minimally log, however in my case when doing this I'm getting full logging. My database is in the simple recovery model and I successfully get minimally logged inserts on a heap with no indexes and `TABLOCK`.

I'm using an old backup of the Stack Overflow database to test on and have created a replicate of the Posts table with the following schema...

    CREATE TABLE [dbo].[PostsDestination](
    	[Id] [int] NOT NULL,
    	[AcceptedAnswerId] [int] NULL,
    	[AnswerCount] [int] NULL,
    	[Body] [nvarchar](max) NOT NULL,
    	[ClosedDate] [datetime] NULL,
    	[CommentCount] [int] NULL,
    	[CommunityOwnedDate] [datetime] NULL,
    	[CreationDate] [datetime] NOT NULL,
    	[FavoriteCount] [int] NULL,
    	[LastActivityDate] [datetime] NOT NULL,
    	[LastEditDate] [datetime] NULL,
    	[LastEditorDisplayName] [nvarchar](40) NULL,
    	[LastEditorUserId] [int] NULL,
    	[OwnerUserId] [int] NULL,
    	[ParentId] [int] NULL,
    	[PostTypeId] [int] NOT NULL,
    	[Score] [int] NOT NULL,
    	[Tags] [nvarchar](150) NULL,
    	[Title] [nvarchar](250) NULL,
    	[ViewCount] [int] NOT NULL
    )
    CREATE NONCLUSTERED INDEX ndx_PostsDestination_Id ON PostsDestination(Id)

I then try to copy the posts table into this table...

    INSERT INTO PostsDestination WITH(TABLOCK)
    SELECT * FROM Posts ORDER BY Id 

From looking at fn_dblog and the log file usage I can see I'm not getting minimal logging from this. I've read that versions before 2016 require trace flag 610 to minimally log to indexed tables, I've also tried setting this but still no joy.

I'm guessing I'm missing something here?

## More information

To add more info I'm using the following procedure that I've written to try to detect minimal logging, maybe I've got something here wrong...

    /*
    	Example Usage...
    
    	EXEC sp_GetLogUseStats
       @Sql = '
          INSERT INTO PostsDestination
          SELECT TOP 500000 * FROM Posts ORDER BY Id ',
       @Schema = 'dbo',
       @Table = 'PostsDestination',
       @ClearData = 1
    
    */
    
    CREATE PROCEDURE [dbo].[sp_GetLogUseStats]
    (	
       @Sql NVARCHAR(400),
       @Schema NVARCHAR(20),
       @Table NVARCHAR(200),
       @ClearData BIT = 0
    )
    AS
    
    IF @ClearData = 1
       BEGIN
       TRUNCATE TABLE PostsDestination
       END
    
    /*Checkpoint to clear log (Assuming Simple/Bulk Recovery Model*/
    CHECKPOINT	
    
    /*Snapshot of logsize before query*/
    CREATE TABLE #BeforeLogUsed(
       [Db] NVARCHAR(100),
       LogSize NVARCHAR(30),
       Used NVARCHAR(50),
       Status INT
    )
    INSERT INTO #BeforeLogUsed
    EXEC('DBCC SQLPERF(logspace)')
    
    /*Run Query*/
    EXECUTE sp_executesql @SQL
    
    /*Snapshot of logsize after query*/
    CREATE TABLE #AfterLLogUsed(	
       [Db] NVARCHAR(100),
       LogSize NVARCHAR(30),
       Used NVARCHAR(50),
       Status INT
    )
    INSERT INTO #AfterLLogUsed
    EXEC('DBCC SQLPERF(logspace)')
    
    /*Return before and after log size*/
    SELECT 
       CAST(#AfterLLogUsed.Used AS DECIMAL(12,4)) - CAST(#BeforeLogUsed.Used AS DECIMAL(12,4)) AS LogSpaceUsersByInsert
    FROM 
       #BeforeLogUsed 
       LEFT JOIN #AfterLLogUsed ON #AfterLLogUsed.Db = #BeforeLogUsed.Db
    WHERE 
       #BeforeLogUsed.Db = DB_NAME()
    
    /*Get list of affected indexes from insert query*/
    SELECT 
       @Schema + '.' + so.name + '.' +  si.name AS IndexName
    INTO 
       #IndexNames
    FROM 
       sys.indexes si 
       JOIN sys.objects so ON si.[object_id] = so.[object_id]
    WHERE 
       si.name IS NOT NULL
       AND so.name = @Table
    /*Insert Record For Heap*/
    INSERT INTO #IndexNames VALUES(@Schema + '.' + @Table)
    
    /*Get log recrod sizes for heap and/or any indexes*/
    SELECT 
       AllocUnitName,
       [operation], 
       AVG([log record length]) AvgLogLength,
       SUM([log record length]) TotalLogLength,
       COUNT(*) Count
    INTO #LogBreakdown
    FROM 
       fn_dblog(null, null) fn
       INNER JOIN #IndexNames ON #IndexNames.IndexName = allocunitname
    GROUP BY 
       [Operation], AllocUnitName
    ORDER BY AllocUnitName, operation
    
    SELECT * FROM #LogBreakdown
    SELECT AllocUnitName, SUM(TotalLogLength)  TotalLogRecordLength 
    FROM #LogBreakdown
    GROUP BY AllocUnitName

Inserting into a heap with no indexes and TABLOCK using following code...

    EXEC sp_GetLogUseStats
       @Sql = '
          INSERT INTO PostsDestination
          SELECT * FROM Posts ORDER BY Id ',
       @Schema = 'dbo',
       @Table = 'PostsDestination',
       @ClearData = 1

I get these results

[![enter image description here][1]][1]

At 0.0024mb log file growth, very small log record sizes and very few of them I'm happy that this is using minimal logging.

If I then create a non clustered index on id...


    CREATE INDEX ndx_PostsDestination_Id ON PostsDestination(Id)

Then run my same insert again...

[![enter image description here][2]][2]


Not only am I not getting minimal logging on the non clustered index but I've also lost it on the heap. After doing some more tests it seems if I make ID clustered it does minimally log but from what I've read 2016+ should minimally log to a heap with non clustered index when tablock is used. 

I've reported the behaviour to Microsoft on the [SQL Server UserVoice](https://feedback.azure.com/forums/908035-sql-server/suggestions/34407871-sql-server-2017-minimal-logging-not-behaving-as-d) and will update if I get a response. I've also written up the full details of the minimal log scenarios that I couldn't get to work at https://gavindraper.com/2018/05/29/SQL-Server-Minimal-Logging-Inserts/


  [1]: https://i.stack.imgur.com/pvmcA.png
  [2]: https://i.stack.imgur.com/k8G7D.png
  [3]: https://feedback.azure.com/forums/908035-sql-server/suggestions/34407871-sql-server-2017-minimal-logging-not-behaving-as-do