Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Related to this question. –  Nick Chammas Nov 30 '11 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 '13 at 21:28
add comment

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.

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.

share|improve this answer
    
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? –  Nick Chammas Nov 30 '11 at 20:04
    
Is there an easy way to measure how much logging a transaction produces so I can compare approaches quantitatively? –  Jack Douglas Nov 30 '11 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 '11 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? –  Nick Chammas Nov 30 '11 at 20:32
1  
Trace flag 610 is not needed when INSERTing into an empty clustered table. –  Gonsalu Dec 2 '11 at 11:52
show 5 more comments

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.

share|improve this answer
add comment

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?

share|improve this answer
    
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 –  Jack Douglas Nov 30 '11 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 '11 at 1:12
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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