Why does ALTER COLUMN to NOT NULL cause massive log file growth?
I have a table with 64m rows taking 4.3 GB on disk for its data.
Each row is about 30 bytes of integer columns, plus a variable
NVARCHAR(255) column for text.
I added a a NULLABLE column with data-type
I then UPDATED this column for every row and made sure all new inserts place a value in this column.
Once there were no NULL entries I then ran this command to make my new field mandatory:
ALTER TABLE tblCheckResult ALTER COLUMN [dtoDateTime] [datetimeoffset](0) NOT NULL
The result was a HUGE growth in the transaction log size - from 6GB to over 36GB until it ran out of space!
Does anyone have any idea what on earth SQL Server 2008 R2 is doing for this simple command to result in such huge growth?