Here's the scenario:
DB with Simple recovery model,
Roughly 14 GB of data (~80 million rows),
INSERT ... SELECTstatement into empty table with 7 non-clustered, non-unique, single column indexes and one clustered single column primary key from table with no indexes,
- Transaction logs set to have unlimited space with
Autogrowthon by 2 GB
The query has been running for 3 hours now and transaction log files have grown to about 45 GB!
My question is simple - why? What is actually written to transaction log in this scenario? I read this article, this technet resource and this MSDN article but I still don't understand why the log is so big, especially in Simple recovery mode - this operation should be minimally logged...
The table is not replicated. I finally decided to cancel my query and instead create keys/indexes/constraints on new table, drop the old one and rename it. It should be faster, right?