I'm importing a largish amount of data (5M+ rows in 25K batches) but
don't care about recovering if an error happens.  I imagine that
specifying the database with the "Truncate Log on Checkpoint" option
would be slower than issuing  "DUMP TRANSACTION WITH TRUNCATE_ONLY" or
NO_LOG.  I know it's splitting hairs, but I'm really trying to reduce
the time on this process.

Any comments?  Thanks.

jon moorman

Truncate on check point happen automatically about every 60 seconds. You can
issue a check point by executing command CHECKPOINT.

The 'dump tran with no_log' is executed manually.

I haven't had 5m rows, my highest was 1m.  I made the log big enough to old
the updates for 60 seconds.  Then I just let the system clean up the log.

Jon,

The "Truncate Log on Checkpoint" option will be easier, and I can't
think of any reason why it would be slower than dumping with
TRUNCATE_ONLY.

Roy

If you are importing using bcp, drop the indexes on the table you are
importing into, set the 'select into/bulk copy'  and 'truncate log on
checkpoint' options for the database, and then do your import.  Remember to
turn those options off when you are done.  The first option will not log
the inserts, only the allocations of additional pages so that less will be
written to the log.

Matthew Bando
Director of Training Services
West Colebrooke Inc.