How do I minimise logging during a large insert?

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.

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.

EDIT:

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.

StackExchange Database Administrators Q#8614, answer score: 9