Why is the Trans Log larger than the data inserted?

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

Transaction (MB)

Max Log Row size after transaction Number of log rows after transaction Number of large rows after transaction
Simple 1023 132 11928 1225840 2
Full 4351 3278 12328 2048806 387090
Bulk-Logged 4351 222 12328 1327437 2

Summary:

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.

 

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s