[dba-SQLServer] log file size

I am migrating a database to a new container.  My databases tend to be hundreds of gigabytes and 
often times I can cut the size in half by migration.  I also use page compression because it allows 
me to get much more of the data into memory and I have cores to throw at the decompression task.

I make the recovery model simple because they are read-mostly and that works for me.

So I am migrating a couple of tables, each of which contains 225 million records.  The first was 277 
fields but very few indexes.  It took a looong time to move the data and the log file was huge, 
literally over two hundred gigabytes.

The second table was only 22 fields but has indexes covering many of the fields.  Moving this table 
is taking much longer and it appears to be the indexing that is taking all of the time.  The log 
file is 450 gigabytes and growing.  I have another 450 gb of space so I do not anticipate running 
out before it finishes but I am wondering why it takes so much room?  I thought that SQL Server 
committed stuff and then reclaimed the space in the log file to use in the next operation.


-- 
John W. Colby
Colby Consulting

Reality is what refuses to go away
when you do not believe in it