Accounting for Transaction Log Growth

When I copy two columns—approximately 5MB in disk size and containing fewer than 400,000 rows—from one table to another, the copying process seems to enlarge the transaction log by about 100MB, even when I drop the indexes from the destination table. I use the following statement to copy the data:

INSERT into Table_A (Col1, Col2)SELECT Col1, Col2 FROM Table_B

The database is set up to truncate on checkpoint, and autoshrink is turned on. Is it typical for this INSERT statement to cause the transaction log to increase by such a large amount, and if so, what can I do to reduce the log's size?

Based on the information you provided, you can account for about 60MB of log space for your operation. Each row in the INSERT CASE claims about 56 bytes of log-record overhead. So, assuming that each data record uses 12 bytes, each insert would occupy about 70 bytes of log space. Multiply 70 by 400,000 records, then multiply the product (28 million bytes) by 2 to account for the compensation log record's reserved space. So far, the byte consumption amounts to 56MB. But wait—you aren't finished. SQL Server has to allocate about 650 pages to perform the insert, so you can tack on an additional couple of megabytes. The rest of the bloat could be due to the log file's original size. If the table contains a clustered index, the amount of space would increase again because of splitting index pages.

To keep your inserts from increasing the transaction log file so much, execute the inserts in smaller transactions based on an ordering key. And to reduce the log's size, you can also run one of the shrink log space scripts (i.e., DBCC SHRINKFILE and DBCC SHRINKDATABASE) that are available from the Microsoft Developer Network (MSDN) Web site at http://msdn.microsoft.com.