為什麼 ALTER COLUMN to NOT NULL 會導致大量日誌文件增長？

我有一個 64m 行的表，它的數據在磁碟上佔用了 4.3 GB。

每行大約有 30 個字節的整數列，加上一個NVARCHAR(255)用於文本的可變列。

我添加了一個帶有 data-type 的 NULLABLE 列Datetimeoffset(0)。

然後我為每一行更新了這一列，並確保所有新插入都在該列中放置一個值。

一旦沒有 NULL 條目，我就執行此命令以使我的新欄位成為強制性：

ALTER TABLE tblCheckResult 
ALTER COLUMN [dtoDateTime] [datetimeoffset](0) NOT NULL

結果是事務日誌大小大幅增長 - 從 6GB 到超過 36GB，直到空間用完！

有誰知道 SQL Server 2008 R2 到底在為這個簡單的命令做了什麼來導致如此巨大的增長？

當您將列更改為 NOT NULL 時，SQL Server 必須觸及每一頁，即使沒有 NULL 值也是如此。根據您的填充因子，這實際上可能導致大量頁面拆分。當然，每個被觸摸的頁面都必須被記錄，我懷疑由於拆分，可能必須為許多頁面記錄兩個更改。但是，由於這一切都是一次性完成的，因此日誌必須考慮所有更改，這樣，如果您點擊取消，它就會確切地知道要撤消什麼。

---

一個例子。簡單表：

DROP TABLE dbo.floob;
GO

CREATE TABLE dbo.floob
(
 id INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, 
 bar INT NULL
);

INSERT dbo.floob(bar) SELECT NULL UNION ALL SELECT 4 UNION ALL SELECT NULL;

ALTER TABLE dbo.floob ADD CONSTRAINT df DEFAULT(0) FOR bar

現在，讓我們看一下頁面詳細資訊。首先，我們需要找出我們正在處理的頁面和 DB_ID。在我的例子中，我創建了一個名為 的數據庫foo，而 DB_ID 恰好是 5。

DBCC TRACEON(3604, -1);
DBCC IND('foo', 'dbo.floob', 1);
SELECT DB_ID();

輸出表明我對第 159 頁（DBCC IND輸出中唯一帶有的行PageType = 1）感興趣。

現在，讓我們在逐步完成 OP 的場景時查看一些選擇頁面的詳細資訊。

DBCC PAGE(5, 1, 159, 3);

UPDATE dbo.floob SET bar = 0 WHERE bar IS NULL;    
DBCC PAGE(5, 1, 159, 3);

ALTER TABLE dbo.floob ALTER COLUMN bar INT NOT NULL;
DBCC PAGE(5, 1, 159, 3);

現在，我沒有這個問題的所有答案，因為我不是一個內心深處的人。但很明顯 - 雖然更新操作和添加 NOT NULL 約束都不可否認地寫入頁面 - 後者以完全不同的方式這樣做。通過將可空列換成不可空列，它似乎實際上改變了記錄的結構，而不僅僅是擺弄位。為什麼它必須這樣做，我不太確定——我想這對儲存引擎團隊來說是個好問題。我確實相信 SQL Server 2012 可以更好地處理其中一些場景，FWIW - 但我還沒有進行任何詳盡的測試。

引用自：https://dba.stackexchange.com/questions/29522