How to I minimise logging during a large insert?

Question

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.

Answer 1

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.

Answer 2

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.

SELECT
    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
FROM
    sys.database_files f
INNER JOIN  
    sys.dm_io_virtual_file_stats(DB_ID(), NULL) vs
ON  vs.file_id = f.file_id
WHERE
    f.type_desc = 'LOG'
OPTION
    (RECOMPILE) ;

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

Answer 3

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?