Post reply

Will table compression also cause transaction log to be smaller?

  • August 9, 2011 at 3:59 pm

    #1366317

    coder_t2 (8/9/2011)

    Jeffrey Williams-493691 (8/9/2011)

    GilaMonster (8/9/2011)

    Insert in batches or use an insert method that can be minimally logged (and drop any nonclustered indexes before you start)

    Don't drop the nonclustered indexes - disable them instead, then after the data is loaded rebuild all indexes.

    I would recommend inserting in smaller batches instead - or, use SSIS instead which would give you the ability to control the batch size and commit size using the Fast table load option on an OLEDB destination.

    I ended up disabling the nonclustered indexes. This solved my log growth problem. I disable then just rebuild after the insert is done. Thanks for all the help guys.

    It will also almost certainly be faster to load then create/rebuild indexes than to load with the indexes there.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing post 16 (of 15 total)

You must be logged in to reply to this topic. Login to reply