mai.quangvinh (9/9/2014)
My colleague who is a dba help me investigate the issue, she found that the destination table has primary key which is a composite index including 3 column and about 5 more non-clustered indexes. We conclude that when inserting a large number of records to the table, many indexes need to be updated, so that is the root cause that make the transaction log file grow dramatically.I just want to know why the log file is very big. With Eric 's suggestion, I think it is useful if I want to have better performance when performing bulk insert.
I have another concern that whether could we rebuild index and force SQL Server don't write log to transaction log file?
Once again, thanks Grant and Eric for your suggestion.
If you have any followup questions about why this insert and transaction logging behaves the way it does, then reply back with DDL for table and indexes.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho