none
Minimal logging not being used on INSERT INTO ... SELECT FROM

    Question

  • I am trying to perform a big INSERT INTO ... SELECT FROM and I just can’t understand why it isn’t using minimal logging.

    My SQL Server version is:

    Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)

           Mar 29 2009 10:11:52

           Copyright (c) 1988-2008 Microsoft Corporation

           Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) (VM)

    I have tried both the Simple and Bulk Logged recovery models for my database.  Nothing seems to make any difference.  There are no replication publications or subscriptions on the server.

    I have reduced my query down to the simplest thing I can think of.  I have tried lots of variations on this query; nothing seems to make any difference.  Here’s the query:

    DROP TABLE dbo. Tmp_AccountOutput

    CREATE TABLE dbo. Tmp_AccountOutput

          (     

          EntityTypeID int NOT NULL,

          EntityID int NOT NULL,

          ScenarioID int NOT NULL,

          AccountID int NOT NULL,

          PeriodID int NOT NULL,

          Value decimal ( 12, 2) NOT NULL,

          IsComparisonCopy bit NOT NULL,

          IsRefScenario bit NOT NULL

          )   

          INSERT INTO dbo. Tmp_AccountOutput WITH ( TABLOCK ) ( EntityTypeID, EntityID, ScenarioID, AccountID, PeriodID, Value, IsComparisonCopy, IsRefScenario)

                SELECT top 5 EntityTypeID, EntityID, ScenarioID, AccountID, PeriodID, Value, IsComparisonCopy, IsRefScenario FROM dbo. AccountOutput

    GO

    After running the query I run this to see the tx log activity:

    select Operation, Context, [Log Record Fixed Length], [Log Record Length]

    from fn_dblog (null, null)

    where allocunitname= 'dbo.Tmp_AccountOutput'

    order by [Current LSN] desc

    No matter how I write the main query, I always get this output:

    Operation                       Context                         Log Record Fixed Length Log Record Length

    ------------------------------- ------------------------------- ----------------------- -----------------

    LOP_INSERT_ROWS                 LCX_HEAP                         62                      136

    LOP_INSERT_ROWS                 LCX_HEAP                        62                      136

    LOP_INSERT_ROWS                 LCX_HEAP                        62                      136

    LOP_INSERT_ROWS                  LCX_HEAP                        62                      136

    LOP_INSERT_ROWS                 LCX_HEAP                        62                      136

    LOP_FORMAT_PAGE                 LCX_HEAP                        80                      84

    LOP_MODIFY_ROW                  LCX_IAM                         62                      88

    LOP_FORMAT_PAGE                 LCX_IAM                         80                      84

    LOP_MODIFY_ROW                  LCX_PFS                         62                       80

    The number of LOP_INSERT_ROWS entries always matches the number of rows that I selected for insertion.  It seems quite clear that minimal logging is not being used.  Sure enough, when I try to run the job for real the log file balloons in size.

    Can anyone suggest what I might be missing?  Perhaps I really am using minimal logging, but then why does my log file quickly grow to 42GB when I run the full job?

    Thanks in advance.

    Carl

     

    • Moved by Tom PhillipsModerator Tuesday, April 13, 2010 1:32 PM Possibly better answer from TSQL forum (From:SQL Server Database Engine)
    Tuesday, April 13, 2010 9:02 AM

Answers

  • i don't suppose you have any database backups running whilst doing the insert?

    have you tried one of the other methods, to see if it works under those conditions, i.e. 

    -target is an empty B-tree, and you specify TABLOCK

    -target is an empty B-tree, and trace flag 610 is on (TABLOCK is not required)

    -target is non-empty B-tree, and trace flag is on - minimal logging will apply to new key ranges that allocate and populate new pages

    or using a different operations to get the data in, i.e.

    select EntityTypeID, EntityID, ScenarioID, AccountID, PeriodID, Value, IsComparisonCopy, IsRefScenario

    into #test

    FROM dbo. AccountOutput

    assuming that your database is in simple or bulk_logged recovery mode

    Tuesday, April 13, 2010 2:16 PM

All replies

  • the only thing I could suggest is the syntax of your insert into..

     

    INSERT INTO <DestinationTable> (<Columns>) WITH (TABLOCK)

    SELECT <Columns> FROM <SomeStatement>

    i.e.

     

    INSERT INTO dbo. Tmp_AccountOutput ( EntityTypeID, EntityID, ScenarioID, AccountID, PeriodID, Value, IsComparisonCopy, IsRefScenario) WITH ( TABLOCK ) 

                SELECT top 5 EntityTypeID, EntityID, ScenarioID, AccountID, PeriodID, Value, IsComparisonCopy, IsRefScenario FROM dbo. AccountOutput

    GO

    Everything else you've said seems fine to me.. (based on what I know!)

    Cheers, Andy

     

    Tuesday, April 13, 2010 12:20 PM
  • Thanks for your suggestion, however SQL Server doesn't seem to like it.  I get 'incorrect syntax near the keyword 'with''.  The syntax of INSERT is something like this...

    INSERT 
        [ TOP ( expression ) [ PERCENT ] ]
        [ INTO ]
        { <object> | rowset_function_limited
          [ WITH ( <Table_Hint_Limited> [ ...n ] ) ]
        }
    {
        [ ( column_list ) ]

    ... so I think that INSERT INTO table WITH (TABLOCK) is right.  This article on minimal logging also using that syntax.  Is there a way to check whether the TABLOCk is being applied?

    Thanks,

    Carl

    Tuesday, April 13, 2010 12:36 PM
  • Everything is logged in SQL Server.  The logging mode only determines when the log is able to reuse space. 

     

    If the log is growing to 42GBs it is because it needs it.  You either need to break up your query into smaller bits or deal with the log file.

     

    Tuesday, April 13, 2010 1:32 PM
  • Tom,

    I'm not sure if we're talking about the same thing.  The feature that I'm trying to invoke is Minimal Logging, as described in http://msdn.microsoft.com/en-us/library/ms190422.aspx .  The following description from that article sounds like just what I want:

    "For a database under the full recovery model, all row-insert operations that are performed by bulk import are fully logged in the transaction log. Large data imports can cause the transaction log to fill rapidly if the full recovery model is used. In contrast, under the simple recovery model or bulk-logged recovery model, minimal logging of bulk-import operations reduces the possibility that a bulk-import operation will fill the log space. Minimal logging is also more efficient than full logging. ... Although data insertions are not logged in the transaction log during a minimally logged bulk-import operation, the Database Engine still logs extent allocations each time a new extent is allocated to the table."

    As I understand it, in SQL Server 2008 (which I am using), minimal logging should be used for an INSERT INTO ... SELECT FROM statement if certain conditions are met (and I think I meet them).

    Tuesday, April 13, 2010 1:48 PM
  • i don't suppose you have any database backups running whilst doing the insert?

    have you tried one of the other methods, to see if it works under those conditions, i.e. 

    -target is an empty B-tree, and you specify TABLOCK

    -target is an empty B-tree, and trace flag 610 is on (TABLOCK is not required)

    -target is non-empty B-tree, and trace flag is on - minimal logging will apply to new key ranges that allocate and populate new pages

    or using a different operations to get the data in, i.e.

    select EntityTypeID, EntityID, ScenarioID, AccountID, PeriodID, Value, IsComparisonCopy, IsRefScenario

    into #test

    FROM dbo. AccountOutput

    assuming that your database is in simple or bulk_logged recovery mode

    Tuesday, April 13, 2010 2:16 PM
  • Those are good suggestions, thanks.

    While I was trying to test them I found that partial logging suddenly started working fine.  Weird.  As you say, maybe someone was running a backup.

    Thanks again.

    Tuesday, April 13, 2010 4:09 PM
  • This may be why you were having problems validating that minimal logging was being used with your query. Maybe there is some threshold that has to be hit before going from fully logged to minimally logged?

    Using a variation of the example found here (http://blogs.msdn.com/b/sqlserverstorageengine/archive/2008/03/06/minimal-logging-changes-in-sql-server-2008-part-1.aspx) I ran two insert statements. One that inserts the full table and one that inserts only the TOP 10 records, but both INSERT statements use the WITH(TABLOCK) hint to an empty heap on a SIMPLE database, so you would think they would both show similar results.

    However, the statement that inserts 999 records (t_heap) shows minimally logged, while the one that only inserts 10 records (t_heap2) shows as fully logged.

    Example:

    TRUNCATE TABLE t_heap;
    TRUNCATE TABLE t_heap2;
    
    insert into t_heap with (TABLOCK) select * from t_source;
    insert into t_heap2 with (TABLOCK) select TOP 10 * from t_source;

    Results:

    AllocUnitName Operation Context TotalLength AvgLen Cnt
    dbo.t_heap LOP_FORMAT_PAGE LCX_IAM 168 84 2
    dbo.t_heap LOP_MODIFY_HEADER LCX_IAM 84 84 1
    dbo.t_heap LOP_MODIFY_ROW LCX_IAM 88 88 1
    dbo.t_heap LOP_MODIFY_ROW LCX_PFS 6700 90 74
    dbo.t_heap LOP_SET_BITS LCX_GAM 1872 62 30
    dbo.t_heap LOP_SET_BITS LCX_IAM 1872 62 30

    AllocUnitName Operation Context TotalLength AvgLen Cnt
    dbo.t_heap2 LOP_FORMAT_PAGE LCX_HEAP 168 84 2
    dbo.t_heap2 LOP_FORMAT_PAGE LCX_IAM 84 84 1
    dbo.t_heap2 LOP_INSERT_ROWS LCX_HEAP 12120 1212 10
    dbo.t_heap2 LOP_MODIFY_ROW LCX_IAM 184 92 2
    dbo.t_heap2 LOP_MODIFY_ROW LCX_PFS 160 80 2

    Friday, December 03, 2010 5:45 PM