Alter column causing log to fill

I'm trying to simply change a column definition from Null to Not Null. It's
a multi million row table. I've already checked to make sure there are no
nulls for any rows and a default has been created for the column. My log is
set to autogrow and as the alter column colname char(6) Not Null runs the
log begins to grow. If I use no check BOL say the optimizer won't consider
the change. How can I change the nullability of a column that currently
contains no nulls without using up extreme amounts of log space?

DannyDanny (istdrs@.flash.net) writes:
> I'm trying to simply change a column definition from Null to Not Null.
> It's a multi million row table. I've already checked to make sure there
> are no nulls for any rows and a default has been created for the column.
> My log is set to autogrow and as the alter column colname char(6) Not
> Null runs the log begins to grow. If I use no check BOL say the
> optimizer won't consider the change. How can I change the nullability
> of a column that currently contains no nulls without using up extreme
> amounts of log space?

I guess the reason that the log grows, is that SQL Server needs to update
internal data structures in each page on the table. For each row there
is a bitmap that specifies which columns in the row that have the NULL
value. If you take make one column NOT NULL, then the bitmap is affected,
at least if it is not the last column in the map.

CHECK/NOCHECK has nothing to do with it, verifying the constraint does not
take any log space. (And SQL Server won't let you to say that a column is
NOT NULL without checking it, to save its own sanity.)

So the only other option to ALTER TABLE, is to take the long way: Rename
the table, create a new table, insert over, restore indexes, triggers and
constraints, move referencing foreign keys and drop the old table. When
you insert data over, you can do it in batches, and with the database
in simple recovery, the log growth will not be equally excessive. A
variation of this with even less log usage may be to bulk out the data,
and load the new table with BCP.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp