This was a fun question to figure out. At a SQL PASS meeting in Orange County this last week. One of the members asked all the DBAs, “When I use Select Into with Simple or Full recovery mode, why is the transaction log 4 times larger than the data inserted into the table?”
None of the DBAs could answer the question. So I spent after hours testing to find out.
This research paper includes the code to set up your own test. Great for learning more about the Transaction Log. See attached .pdf for the entire research paper: translog_selectinto_research
Transaction log size in each mode after the same “select into” statement was run. The log file was backed up and truncated between each test.
|Type of Recovery Model||Log File size after transaction (MB)||Log file in use after
|Max Log Row size after transaction||Number of log rows after transaction||Number of large rows after transaction|
The transaction log quadruples in size when running a “select into” with Full Recovery Model, because not only the data changes are recorded. Every memory change, lock, latch, split pages, etc. is recorded in the log.
For the Simple model, the log file in use grew from zero to 132MB. Even though “Select Into” is minimally logged in the transaction log for Simple model, memory changes are tracked in the log. Items tracked in Simple model with a “Select Into” were page splits, memory allocation, latches, locks, and create table. Minimal logging does not mean no logging.