What exactly is written to transaction log? Here's the scenario:


DB with Simple recovery model,
Roughly 14 GB of data (~80 million rows),
INSERT ... SELECT statement 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 Autogrowth on 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?
but I still don't understand why the log is so big, especially in Simple recovery mode - this operation should be minimally logged...


No, in simple recovery mode, leaving Operations That Can be minimally Logged, all other operations are fully logged. In simple recovery model what is different is when the checkpoints are going to happen. Checkpoints are fired after transaction commits and when log file grows 70% of its size. For all other scenarios logging in simple recovery is same as full recovery.

Minimal Logging also requires Target Table to meet certain requirements


  Roughly 14 GB of data (~80 million rows),


Were you performing insert into select * from type of query ? In that case you were doing lot of data reading and movement so transaction log file will record each page allocated to new table and depending on query. Now transaction log also keeps space for rollback of all the changes done. Keeping all this in mind I guess the space required would be genuine. I am not sure what query you ran and your SQL Server configuration, depending on that, answer can change.

There was a similar question I answered about what events are recorded in transaction log. There is NO documented command to read it but you can use undocumented command to read transaction log, I must tell you it requires level of expertise to interpret the output. Please read Analyzing Transaction Log By Remus Rusanu.

There are 3rd party tools available by RedGate and Apex which helps you in reading transaction log. You can search about them online.