[dba-SQLServer] log file size

John,

A few questions.

1. What exactly do you mean by "migrate"? Do you really mean "move", or are
you actually migrating, as in from one version or instance to another?
There's a large difference between them.
2. Since your database is, except for the occasional mass-update, for all
intents and purposes, R/O, why bother with the log in either your backups
or your migration or move?

Arthur

On Thu, Oct 4, 2012 at 11:13 AM, jwcolby <jwcolby at colbyconsulting.com>wrote:

> 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.
>
>