September 8, 2014 at 3:54 am
Hi Friends,
I have a concern as follow:
I have a database and the tableA table, and I am sure that no one except me connecting to it.
First, I shrink the log file and then I execute the following statement:
INSERT INTO tableA WITH (TABLOCK) SELECT FROM...
I could not explain why the data file is 6 GB, but when the SQL statement above finishes, the log file grew to nearly 100 GB. There were 13 millions recrods inserted to tableA
Many thanks if you can share me some ideas.
Regards,
September 8, 2014 at 6:34 am
It could be cumulative from a series of transactions. Is your database in full recovery? If so, are you running log backups? If not, that could be the issue right there. A large series of transactions has accumulated to build your log out. It won't be cleared out until you run a log backup.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 8, 2014 at 7:05 am
Ideally, you don't want to shrink your transaction log, because it's more efficient to re-use the same log. However, 100 GB of logging on a 6 GB table (or 6 GB insert?) does seem excessive. One thing to consider is that, if the table has indexes and pre-existing records, then the bulk insert operation won't be minimally logged, even with the (TABLOCK) hint. Also, maintaining indexes on the fly during bulk insert is not efficient and can result in fragmentation.
In a test database, experiment with the following and see if disabling indexes prior to bulk insert and rebuilding indexes afterward actually reduces transaction logging. It may even speed up the operation.
ALTER INDEX ALL ON tableA DISABLE;
INSERT INTO tableA WITH (TABLOCK) SELECT FROM...
ALTER INDEX ALL ON tableA REBUILD;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 8, 2014 at 10:33 pm
Thanks Grant and Eric for your information.
I have a supplement that originally the tableA table has no records, and it is inserted data from only another table and 13 millions records inserted. We monitored the process over 3 times and always see that the log file grows nearly 100 GB.
I will investigate more and get back to you if I find something new.
Regards,
Vinh
September 9, 2014 at 12:36 am
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.
September 9, 2014 at 6:40 am
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply