SQL Server Transaction Log の内部構造を徹底解説：VLF、Log Buffer、Checkpoint まで

はじめに

この記事では、SQL ServerのTransaction Logの内部実装を深掘りします。

InnoDB、PostgreSQL、Oracleに続く4本目です。SQL ServerのTransaction Logは独自の設計が多く、特にVLF（Virtual Log File）という概念が特徴的です。SQL Server 2019 / 2022を中心に、以下を解説します：

• Transaction Logの役割
• Log Record（記録の単位）
• VLF（Virtual Log File）とログファイルの物理構造
• Log Bufferと書き込みの仕組み
• Checkpoint（Indirect Checkpointの登場）
• Recovery Model（復旧モデル）

そもそもTransaction Logって何？

InnoDBのREDO Log、PostgreSQLのWAL、OracleのREDO Logと同じ役割です。WALの原則に従い、データページを変更する前にログをディスクに書きます。

SQL Serverの用語で整理すると：

SQL Server用語          InnoDB用語           PostgreSQL用語        Oracle用語
──────────────────────────────────────────────────────────────────────────────
Transaction Log        REDO Log             WAL                  REDO Log
LSN                    LSN                  LSN                  SCN
Log Buffer             Log Buffer           WAL Buffer           Redo Log Buffer
Log Writer             log_writer           バックエンド自身       LGWR
VLF                    ―                    WALセグメント          Redo Logグループ

SQL ServerのLSNはInnoDBやPostgreSQLと同様に物理位置ベースですが、形式が独特です：

LSN = VLF Sequence Number : Log Block Offset : Slot Number
例:   00000025:00000048:0001

3つの部分からなり、VLF番号・ブロック内オフセット・スロット番号でログレコードの位置を一意に特定します。

Log Recordの構造

SQL Serverでは、各変更がLog Recordとして記録されます。

Log Recordの種類

主要なLog Record Typeはfn_dblog関数で確認できます：

SELECT [Current LSN], [Operation], [Context], [Transaction ID],
       [Page ID], [AllocUnitName]
FROM fn_dblog(NULL, NULL);

Current LSN              Operation        Context       Page ID
------------------------  ---------------  -----------  --------
00000025:00000048:0001   LOP_BEGIN_XACT   LCX_NULL     NULL
00000025:00000048:0002   LOP_INSERT_ROWS  LCX_HEAP     0001:0000009A
00000025:00000048:0003   LOP_INSERT_ROWS  LCX_INDEX_LEAF 0001:000000A1
00000025:00000050:0001   LOP_COMMIT_XACT  LCX_NULL     NULL

主要な操作タイプ： – LOP_BEGIN_XACT / LOP_COMMIT_XACT：トランザクション開始/コミット – LOP_INSERT_ROWS：行の挿入 – LOP_MODIFY_ROW：行の更新 – LOP_DELETE_ROWS：行の削除

InnoDBとの違い：REDO + UNDOが1つのログに

SQL Serverの最大の特徴は、REDO情報とUNDO情報が同じTransaction Logに記録されることです。

InnoDBやOracleではUNDO情報は別の場所に保存されますが、SQL ServerではTransaction Logに「この変更を元に戻すにはこうする」という情報も一緒に記録されます。これにより、ロールバック時もTransaction Logだけで完結します。

VLF（Virtual Log File）

SQL Serverのログファイル管理で最も特徴的なのがVLFです。

VLFとは

物理的なログファイル（.ldf）は内部的にVLF（Virtual Log File）という論理単位に分割されます。VLFはログの循環利用と管理の単位です。

物理ログファイル (.ldf)
| VLF 1 | VLF 2 | VLF 3 | VLF 4 | VLF 5 |
|---|---|---|---|---|
| INACTIVE | ACTIVE | ACTIVE | CURRENT | FREE |

各VLFのステータス：
- **ACTIVE**：アクティブなトランザクションやリカバリに必要
- **CURRENT**：現在書き込み中
- **FREE / INACTIVE**：再利用可能

InnoDBはファイル全体を循環利用し、PostgreSQLはセグメントファイルを作成/削除し、Oracleはグループ単位で切り替えます。SQL ServerはVLFという**ファイル内の論理区画**で管理するのが独特です。

### VLFの確認

```sql
DBCC LOGINFO;

FileId  FileSize    StartOffset  FSeqNo  Status  Parity  CreateLSN
------  ----------  -----------  ------  ------  ------  ---------
2       2031616     8192         37      2       64      0
2       2031616     2039808      38      2       64      0
2       2031616     4071424      39      2       128     0
2       2031616     6103040      40      0       0       0

• Status 2：アクティブ（再利用不可）
• Status 0：非アクティブ（再利用可能）
• FSeqNo：VLFのシーケンス番号

VLF数の問題

ログファイルの拡張方法によってVLF数が変わります。小刻みに自動拡張を繰り返すとVLFが大量に作られ、リカバリ性能が劣化します。

-- 悪い例：小さい自動拡張 → VLFが大量に
ALTER DATABASE MyDB MODIFY FILE (NAME = MyDB_log, FILEGROWTH = 1MB);

-- 良い例：適切なサイズで事前確保
ALTER DATABASE MyDB MODIFY FILE (NAME = MyDB_log, SIZE = 8GB, FILEGROWTH = 1GB);

VLF数の目安：数十〜数百が適正。数千以上は問題です。

Partial Write問題：SQL Serverはどう対処するか？

InnoDBはDouble Write Buffer、PostgreSQLはFull Page Write、Oracleは512Bブロック書き込み＋Checksumで対処していました。SQL Serverはどうでしょうか？

Transaction Log側：問題になりにくい

SQL ServerのTransaction Logも512バイトのLog Block単位で書き込まれます。Oracleと同様に、セクタサイズの原子書き込みが保証される範囲なので、ログ側のPartial Writeは問題になりにくいです。

データページ側：Torn Page Detection

SQL Serverはデータページ（8KB）のPartial Write対策としてTorn Page Detectionを提供します：

-- Torn Page Detectionを有効化
ALTER DATABASE MyDB SET PAGE_VERIFY TORN_PAGE_DETECTION;

-- より強力なChecksum検証（SQL Server 2005〜、デフォルト）
ALTER DATABASE MyDB SET PAGE_VERIFY CHECKSUM;

• TORN_PAGE_DETECTION：各512Bセクタにビットを記録し、ページが完全に書かれたか検証
• CHECKSUM（デフォルト）：ページ全体のチェックサムで破損を検出

Oracleと同様に「破損を検出する」アプローチです。InnoDBのDouble Write BufferやPostgreSQLのFull Page Writeのように「ログから復元する」アプローチとは異なります。

Log Bufferと書き込み

Log Buffer

SQL ServerのLog Bufferはメモリ上の循環バッファで、サイズは自動管理されます（通常数MB）。InnoDBやOracleのように明示的にサイズを設定するパラメータはありません。

書き込みの流れ

SQL Serverの書き込みモデルはOracleに近く、Log Writerという専用スレッドがディスクへの書き込みを担当します：

1. ワーカースレッド
   ├─ Log Recordを生成
   ├─ Log Bufferにコピー（spinlockで保護）
   └─ コミット時にLog Writerに通知

2. Log Writer
   ├─ Log BufferからディスクにI/O発行
   └─ 完了後、待機中のワーカースレッドを起床

Log Block

Log Bufferのデータは512バイトのLog Block単位でディスクに書かれます。InnoDBのBlockサイズと同じです。

Log Block (512 bytes):
- Log Block Header
- Log Records ...
- Padding (残り領域)

### 各DBの書き込みモデル比較

| | InnoDB | PostgreSQL | Oracle | SQL Server |
|---|---|---|---|---|
| 書き込み主体 | log_writer | 各バックエンド | LGWR | Log Writer |
| 並列挿入 | Lock-free（fetch_add） | LWLock（8スロット） | Private Redo Strand | Spinlock |
| Group Commit | あり | あり（fsync共有） | あり | あり |

## Checkpoint

### 従来のCheckpoint

SQL Server 2012以前は、`recovery interval`設定に基づいて定期的にCheckpointが発生し、ダーティページをディスクにフラッシュしていました。PostgreSQLのCheckpointに近い動作です。

### Indirect Checkpoint（SQL Server 2016〜）

SQL Server 2016以降、**Indirect Checkpoint**がデフォルトになりました。これはOracleのIncremental CheckpointやInnoDBのPage Cleanerに近い考え方です。

```sql
-- Target Recovery Time: リカバリにかかる目標時間
ALTER DATABASE MyDB SET TARGET_RECOVERY_TIME = 60 SECONDS;  -- デフォルト

Indirect Checkpointでは、バックグラウンドで継続的にダーティページをフラッシュします。目標リカバリ時間（TARGET_RECOVERY_TIME）を達成できるペースでフラッシュを進めます。

従来のCheckpoint:
  ────────────────┤ Checkpoint! 全ダーティページフラッシュ ├────────
                   ↑ I/Oスパイク

Indirect Checkpoint:
  ──╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌──
    ↑ 常時少しずつフラッシュ（I/Oが平準化）

各DBのCheckpoint比較

Recovery Model（復旧モデル）

SQL Server固有の重要な概念がRecovery Modelです。ログの保持方針を決定します。

-- 確認
SELECT name, recovery_model_desc FROM sys.databases;

-- 変更
ALTER DATABASE MyDB SET RECOVERY FULL;

他のDBとの対応：

FULLモデルでログバックアップを取らないと、ログファイルが際限なく肥大化します。これはSQL Server運用で最もよくあるトラブルの1つです。

まとめ

SQL Serverの特徴は、REDO/UNDOを1つのログに統合している点と、VLFによるファイル内論理管理です。Indirect Checkpointの導入により、OracleやInnoDBと同様にI/Oの平準化が実現されています。

次回はDb2 Transaction Logを解説し、最後にシリーズ全体の比較表でまとめます。

参考文献

• SQL Server Transaction Log Architecture and Management Guide
• SQL Server Documentation: Virtual Log Files (VLFs)
• SQL Server Documentation: Database Checkpoints
• SQL Server Documentation: Recovery Models
• SQL Server Documentation: sys.fn_dblog