During a nightly scheduled task I am creating a large table from scratch using select into and then adding a clustered index. I would like to minimise the amount of logging during the whole process as I have limited bandwidth for log backup shipping.

I'm happy to consider an alternative approach if that would help.

  • Related to this question. Nov 30, 2011 at 20:03
  • Do keep in mind that if you do the insert an a minimally logged command then the transaction log backup will contain the actual data pages used by the insert instead of the insert command. While the log will be smaller, the log backups may actually be larger.
    – mrdenny
    Jul 13, 2013 at 21:28

3 Answers 3


In my experience, the best way to do this is to:

  • Create the EMPTY table
  • Create the clustered index (matching your source table)
  • INSERT using TABLOCK hint for minimal logging

Details are included in this MS Whitepaper.

Creating the table first, then indexing it on the same key as the source eliminates sorting.

If the keys match, TABLOCK is used, and trace flag 610 is enabled, the operation will be minimally logged (logging pages not transactions or rows).

For me this is faster as adding the clustered index after SELECT INTO forces a sort which can be very costly and time consuming.


TF 610 is used solely to control minimal logging behavior when inserting into indexed tables.

I'm not aware of any issues with using it, but MS recommends (rather defensively I think) to test before using.

  • Can you elaborate a bit on trace flag 610? What's it for, are there any things to watch for when it is enabled, etc? Nov 30, 2011 at 20:04
  • Is there an easy way to measure how much logging a transaction produces so I can compare approaches quantitatively? Nov 30, 2011 at 20:05
  • @JackDouglas - I'm not sure since I'm more concerned with the time spent as opposed to log footprint. If you use full recovery model I don't think this will work since minimal logging is not enabled.
    – JNK
    Nov 30, 2011 at 20:13
  • @JackDouglas - With JNK. I would compare approaches on execution time since that's what really matters. If logging is the limiting factor in this process then the faster one is the one that logs less. If it isn't, do you care if something logs more but runs faster? Nov 30, 2011 at 20:32
  • 1
    Trace flag 610 is not needed when INSERTing into an empty clustered table.
    – gonsalu
    Dec 2, 2011 at 11:52

Is there an easy way to measure how much logging a transaction produces so I can compare approaches quantitatively?

Simplistic approach, you could record current log usage from the dm_io_virtual_file_stats DMV before and after your batch process. This would be polluted by other server activity however, so only useful if you can test in isolation.

    DB_NAME(DB_ID()) AS [Database Name]
  , vs.[file_id]
  , vs.num_of_reads
  , vs.num_of_bytes_read
  , vs.num_of_writes
  , vs.num_of_bytes_written
    sys.database_files f
    sys.dm_io_virtual_file_stats(DB_ID(), NULL) vs
ON  vs.file_id = f.file_id
    f.type_desc = 'LOG'

Better tool for the task is sp_whoisactive, which reports per transaction log statistics if the parameter @get_transaction_info is set.


Since it is during a maintenance window, are you allowed to do something "creative" like switch the recovery model to simple recovery model, do your work, switch back to full recovery model, and then take a full backup?

  • yes, but I will then need to ship that full backup off site every night wouldn't I? (instead of once a week) - which would outweigh the bandwidth benefit of reducing the logging Nov 30, 2011 at 22:59
  • 1
    That would be a horrible idea as he is doing log shipping and that would then require shipping a full backup to the remote site daily.
    – mrdenny
    Dec 1, 2011 at 1:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.