• Ok, Thomas... it's official.  You're not actually losing your mind. 😀  Minimal logging on the second insert does NOT work as advertised either on 2008 ( Dev Edition) or 2016 (Enterprise Edition).  I even explicitly enabled Trace Flag 610 on both just to be sure.

    I will say that sp_lock still shows that the 2nd insert is a "Bulk Operation" but the logfile still grows the same amount whether it's bulk logged or not.  The second insert also takes the same amount of time whether it's bulk logged or not (WITH (TABLOCK) being used or not is the only difference).  If you make the mistake of leaving out the OPTION (RECOMPILE on the second insert, then you can have a problem where the insert takes nearly 3 times longer especially when a background CHECKPOINT comes into play on smaller memory machines.

    If we look at  the "Pre-requisites for minimal logging at the following URL...
    https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms190422%28v%3dsql.105%29
    ... it clearly states (including the note)...

  • If the table has a clustered index and is empty, both data and index pages are minimally logged. In contrast, if a table has a clustered index and is non-empty, data pages and index pages are both fully logged regardless of the recovery model.

    Note
    If you start with an empty table and bulk import the data in batches, both index and data pages are minimally logged for the first batch, but from the second batch onwards, only data pages are bulk logged.

  • ... which is a direct contradiction of what it say in the link you pointed out, which is also the incorrect one because you've seen it not be true and so have I (now).

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)