network profile
| bio | website | |
|---|---|---|
| location | ||
| age | ||
| visits | member for | 5 months |
| seen | May 10 at 21:26 | |
| stats | profile views | 15 |
|
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? |
