163 reputation
25
bio website
location
age
visits member for 1 year, 4 months
seen May 21 '13 at 15:30

Jul
25
awarded  Notable Question
Mar
4
awarded  Popular Question
Dec
5
awarded  Good Question
Nov
30
comment Why does ALTER COLUMN to NOT NULL cause massive log file growth?
@Martin: READ_COMMITTED_SNAPSHOT is OFF (using SELECT is_read_committed_snapshot_on FROM sys.databases WHERE name= 'mydbname'). I'm not sure how to find out about ALLOW_SNAPSHOT_ISOLATION? When I run SELECT snapshot_isolation_state from sys.databases where name='mydbname' I get 0
Nov
30
comment Why does ALTER COLUMN to NOT NULL cause massive log file growth?
Snapshot isolation is off in my database.
Nov
30
comment Why does ALTER COLUMN to NOT NULL cause massive log file growth?
You guys are talking way above my head! snapshot isolation is off on my database. Can you explain your conclusions in simpler terms at all?
Nov
30
awarded  Scholar
Nov
30
awarded  Supporter
Nov
30
comment Why does ALTER COLUMN to NOT NULL cause massive log file growth?
Wow! Is there any way to preview how much space the ALTER command will take BEFORE you actually do it?
Nov
30
accepted Why does ALTER COLUMN to NOT NULL cause massive log file growth?
Nov
30
comment Why does ALTER COLUMN to NOT NULL cause massive log file growth?
@Aaron - Thanks for the fantastic, detailed reply! I had no idea SQL Server 2008 would have to touch every page. I also had no idea a NULL bitmap was stored for each page too. After much Googling I now understand why this is done. But I assumed, like in SQL Server 2012, flagging a field as NOT NULL would just add a metadata entry at the table definition level!. Even after your explanation and playing around with calcs in a spreadsheet, I still don't fully understand why the log file growth was SO huge. But I think the answer is in here somewhere. Thank you for the time.
Nov
30
comment Why does ALTER COLUMN to NOT NULL cause massive log file growth?
Wow, it looks like 2012 works the way I assumed 2008 would work - i.e. entering the NOT NULL attribute as metadata at table level. To quote BOL: "This means that the operation is completed almost instantaneously regardless of the number of rows in the table."
Nov
29
awarded  Nice Question
Nov
29
awarded  Student
Nov
29
asked Why does ALTER COLUMN to NOT NULL cause massive log file growth?