Sql-server – Why does this 120 GB insert grow the log by over 780 GB

sql server

A table [tbl_old] has about 3 billion rows and about 120 GB for disk space, and then created a [tbl_new] with the same schema (actually, just dumped the script of [tbl_old], then rename the table name to [tbl_new], then create it).

But when running this SQL script:

insert [tbl_new] 
   select * from [tbl_old]

After the transaction log file used up about 780 GB and hung.

My questions are:

  • Is it reasonable for just such inserting script for about 120 GB, more than 780 GB transaction log is not enough?

  • What is in the log file?

Background:

  1. the DB is in simple recovery mode, and no index on these [tbl_old] and [tbl_new]
  2. SQL Server 2008 R2 Enterprise on Window Server 2008 64bit.
  3. And Intel Xeon CPU E5645 @2.4GHz (2 processors), 24 CPUs, 64 GB memory

Best Answer

The log contains an exact description of every inserted record. That is at least 120GB, but must be multiplied by some factor for additional info. 1.5x is a good estimate => ~180Gb. Every record must reserve in the log space for compensation on rollback. 1:1 ratio between do and undo is a good estimate => ~360Gb. Now 780Gb is more than double this estimate. There could be explanations for such high size, like any other concurrent activity in the DB. You can use fn_dblog to investigate how exactly the log is used, if you must know.

Remember that SELECT ... INTO ... FROM ... is an operation that can be minimally logged but INSERT ... SELECT ... FROM ... is actually not, unless the special trace flag 610 is used, see Minimal Logging changes in SQL Server 2008 for details on all requirements to achieve minimally logging with INSERT ... SELECT.

Related Question