locked
INSERT INTO TABLE (WITH NO LOGGING) RRS feed

  • Question

  • Hi all,

    I have a requirement like insert data into stage table and then main table, I am using stored procedure to Insert data by using some validation and the data size is in GB's.
    What exactly I wants, when I am running the Stage Table Insert from Temp then my Transaction log is increasing very heaving which I don't want for my Stage DB actually. If it is for Main DB it is ok as I need the Log and both the DB are important.

    Now my question is "Is there any way to turn off the Logging process" when data is inserting to Stage DB,

    I found one way of "SELECT INTO” is minimal Log but I don't want to use this to create this table always.


    Please help, how can i design wise make this possible or in any TSQL query?

    Regards,
    Sql2011

    Monday, May 3, 2010 6:28 AM

Answers

  • There is no way to insert without logging at all. SELECT INTO is the best way to minimize logging in T-SQL, using SSIS you can do the same sort of light logging using Bulk Insert.

    From your requirements, I would probably use SSIS, drop all constraints, especially unique and primary key ones, load the data in, add the constraints back.  I load about 100GB in just over an hour like this, with fairly minimal overhead.  I am using BULK LOGGED recovery model, which just logs the existence of new extents during the logging, and then you can remove them later.

    The key is to start with barebones tables, and it just screams.  Building the index once leaves you will no indexes to maintain, just the one index build per index.


    Louis

    Tuesday, May 4, 2010 3:10 AM

All replies

  • Hi,

    What version of SQL Server are you using?  Will the staging table have data in it when new data is loaded?

    On the staging database, you could try setting the recovery model to simple or bulk logged, and using the WITH (TABLOCK) condition in your statement - if the table is an empty b-tree.  

    Or, use Select INTO... with simple/bulk recovery model

    Cheers,
    Andy

    Monday, May 3, 2010 2:20 PM
  • There is no way to insert without logging at all. SELECT INTO is the best way to minimize logging in T-SQL, using SSIS you can do the same sort of light logging using Bulk Insert.

    From your requirements, I would probably use SSIS, drop all constraints, especially unique and primary key ones, load the data in, add the constraints back.  I load about 100GB in just over an hour like this, with fairly minimal overhead.  I am using BULK LOGGED recovery model, which just logs the existence of new extents during the logging, and then you can remove them later.

    The key is to start with barebones tables, and it just screams.  Building the index once leaves you will no indexes to maintain, just the one index build per index.


    Louis

    Tuesday, May 4, 2010 3:10 AM
  • This post has saved me from many a heartache. My stored procedure in the SSIS Execute SQL Task was originally set to INSERT INTO the destination table more than 200million rows which are a result of a series of recursive CTE computations. With the INSERT INTO clause, the log files have been drinking up all the allocated resources in log files, i.e. 100GB and as a result I eventually have been getting log file space errors. When I changed my syntax from INSERT INTO to SELECT INTO as recommended in this thread, use of log file resources surprisingly dropped with a very wide margin. Amazing!!!! Thank you for this.


    Mpumelelo

    Wednesday, April 17, 2013 9:54 AM